What is CAML?
Ø CAML - Collaborative Application Markup Language
Ø XML- Extensible Markup Language based query language
Ø Used to perform a query operation against SharePoint Lists
How SharePoint List Items are retrieved?
SharePoint List data can be retrieved in any one of the following ways:
1. Using the SharePoint object model – used when code runs on the server (Example: Developing a web part or an application page)
2. Using the SharePoint Lists web service – used when your code doesn’t run on the server where the SharePoint is installed (Example: Developing a windows application)
3. Using Power shell –used mostly by the ADMIN of the SharePoint when they quickly want to retrieve some information from the SharePoint site
How does CAML query looks like?
As I already mentioned, it is XML based query language and it contains tags in it. The root element of the CAML query root element is Query. But it is not necessary to use Query element in the query you form.
Within the Query element you have two elements possible:
1. Where – to filter the data
2. OrderBy – to categorize the data
A simple structure of the CAML query is as follows:
<Query>
<Where>
<Eq>
<FieldRef Name=”FieldName” />
<Value Type=”DataType”>Value</Value>
</Eq>
</Where>
<OrderBy>
<FieldRef Name=”FieldName” />
<FieldRef Name=”FieldName” />
</OrderBy>
</Query>
Operators in CAML Query
From the above structure, we came to know that it uses Where and OrderBy elements to retrieve the data from the list.
Let us know about the operators present in the CAML query and its usage:
Inside the Where element
1. Logical Operators - AND, OR
2. Comparison Operators - Listed Below in the table
AND – Which takes two conditions are satisfied
OR – Which takes when either of the conditions is satisfied
Inside the OrderBy/GroupBy element
OrderBy – Which orders or sort the data depends upon the field (FieldRef element) given.
GroupBy – Which groups the data depends upon the group by field (FieldRef element) given.
Examples
Logical & Comparison Operators
Use of AND, Gt, Leq
<Query>
<Where>
<And>
<Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
</Gt>
<Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
</Leq>
</And>
</Where>
</Query>
Use of OR, Gt, Leq
<Query>
<Where>
<Or>
<Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
</Gt>
<Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
</Leq>
</Or>
</Where>
</Query>
Use of BeginsWith, Leq
<Query>
<Where>
<And>
<BeginsWith>
<FieldRef Name="Title" />
<Value Type="Text">M</Value>
</BeginsWith>
<Leq>
<FieldRef Name="Quantity" />
<Value Type="Number">1000</Value>
</Leq>
</And>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="False" />
</OrderBy>
</Query>
OrderBy Operator
<Query>
<Where>
<Or>
<Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
</Gt>
<Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
</Leq>
</Or>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="True" />
</OrderBy>
</Query>
Is it possible to write this queries without any errors manually?
Yes. But we will know the errors only after executing the program. Hence there is a free CAML query builder which will generate the query easily.
Caml Queries to Remember
No Filter
<View><ViewFields><FieldRef Name="Title" /><FieldRef Name="Column2" /></ViewFields></View>
With Row Limit
<View><RowLimit>10</RowLimit><ViewFields><FieldRef Name="Title" /><FieldRef Name="Column2" /></ViewFields></View>
Above two can be used with CamlQuery object as view xml.
example
CamlQuery camlquery = new CamlQuery();
camlquery.ViewXml = "<View><RowLimit>10</RowLimit><ViewFields><FieldRef Name='Title'/><FieldRef Name='FileRef'/><FieldRef Name='BasePath'/></ViewFields></View>";
List sSCConfigList = clientContext.Web.Lists.GetByTitle("SSCConfig");
clientContext.Load(sSCConfigList);
clientContext.ExecuteQuery();
var listItems = sSCConfigList.GetItems(camlquery);
Single Select
Example
<Query><Where><Eq><FieldRef Name="Title" /><Value Type="Text">ABC VALUE</Value></Eq></Where></Query>
Multiple Rows
Example
<Query><Where><Contains><FieldRef Name="Title" /><Value Type="Text">Some Text</Value></Contains></Where></Query>
In case of Rich Text field, you can use <![CDATA[]]> around the value to prevent parsing errors when passing HTML into the query. Or you can replace < with <, > with > and “ with " and so on.
Other operators are <Geq/> for greater than and <Leq/> for less than
LookUps
Here we use LookupId=”true”. By using this we can specify the id value in the value tag
Look up on Id
example
<Query><Where><Eq><FieldRef Name="SomeLookupcolumn" LookupId="TRUE" /><Value Type="Lookup">4</Value></Eq></Where></Query>
Here It will filter on ‘SomeLookupColumn’ column here based on look up id and not value. This ensures unique value.
This can be used to get person or group also
example
Filter on Current User
Example
<Query><Where><Eq><FieldRef Name="Author" LookupId="TRUE" /><Value Type="Integer"><UserID /></Value></Eq></Where></Query>
Notice here Valuetype is integer.
Using <UserID /> as the value, the query will filter based on the current user. You can also pass the ID of a specific user in place of <UserID /> (e.g. <Value Type="Integer">283</Value>) if you don’t want to filter by the current user.
Lookup on text
Example
<Query><Where><Eq><FieldRef Name="SomeLookupColumn" /><Value Type="Lookup">GujaratState</Value></Eq></Where></Query>
This will look for items with “GujaratState” in the look up column field. If there are more than one items having same display name, it will return all those items.
Date & Time
Example
<Query><Where><Eq><FieldRef Name="Modified" /><Value Type="DateTime"><Today /></Value></Eq></Where></Query>
A date can also be used instead in Value tag. We can also use something like this <Today OffsetDays="-4" />
