The Collaborative Application Markup Language (CAML) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services.
Example:
<Query>
<Where>
<And>
<And>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Janssens</Value>
</Eq>
<Geq>
<FieldRef Name='Age' />
<Value Type='Number'>21</Value>
</Geq>
</And>
<Lt>
<FieldRef Name='Age' />
<Value Type='Number'>60</Value>
</Lt>
<And>
</Where>
</Query>
How to Build CAML?
It is an XML-based query language. Its root element is Query. Within the Query element two other elements are possible but not mandatory: the OrderBy element and the Where element.
The OrderBy element is the simplest one. It is used to sort the returning list items. You have to specify the field(s) on which you want to sort the items and the sort direction. The syntax looks as follows:
<OrderBy>
<FieldRef Name='LastName' Ascending='False' />
</OrderBy>
If you omit the Ascending attribute, your resulting rows will be sorted in ascending order. If you want to order in descending order you have to specify Ascending=’False’.
The GroupBy clause is used for grouping:
<GroupBy>
<FieldRef Name="Modified"/>
</GroupBy>
<Where>
<Or>
<Neq>
<FieldRef Name="Status"></FieldRef>
<Value Type="Text">Completed</Value>
</Neq>
<IsNull>
<FieldRef Name="Status"></FieldRef>
</IsNull>
</Or>
</Where>
The Where clause is used to specify one or more filter criteria. In its most simple form you specify an operator, a field name for which you want to specify a criterion, and a value.
<Where>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Janssens</Value>
</Eq>
</Where>
Fields
The FieldRef element can be any field of the list on which you want to execute the CAML query. If you use the Name attribute you need to specify the internal name of the field. But you can also use the ID attribute to specify the Guid of the field.
Value
The Value element specifies the value part of the criterion. The attribute Type is optional and specifies the data type of the field you want to specify the criterion for. If omitted the data type is considered as being Text. In all other cases you have to specify the Type attribute.
If the field type is a Lookup you need to specify the text value. For example you have an Employees list and the Country field is a lookup field referring to the Countries list. In that case an employee living in Belgium will have f.e. following value: #15; Belgium. If you have to query for the employees living in Belgium you will have to write your query as follows:
<Where>
<Eq>
<FieldRef Name='Country' />
<Value Type='Lookup'>Belgium</Value>
</Eq>
</Where>
You can find this not good coding practice because the name of the country can change in time. In that case you can also query on the id of the country specifying the LookupId attribute in the FieldRef element:
<Where>
<Eq>
<FieldRef Name='Country' LookupId='True' />
<Value Type='Lookup'>15</Value>
</Eq>
</Where>
Escape sequence:
The ScriptEncode method escapes characters that would otherwise conflict with script.
Original | Replacement |
" | \" |
\ | \\ |
+ | \u002b |
> | \u003e |
< | \u003c |
' | \u0027 |
Example:
<FieldRef Name=\"Checkbox\"></FieldRef> <Value Type=\"bit\">1</Value>
Few of the commonly used operators list:
Operator | Description |
Eq | Equals (=) |
Neq | Not Equals ( <>) |
Gt | Greater than (>) |
Geq | Greater or Equal to (>=) |
Lt | Less than (<) |
Leq | Less than or Equal to (<=) |
IsNull / IsNotNull | Null / Not Null |
BeginsWith | Begins With Word |
Contains | Like |
And | Used within the ‘Where’ element to group filters in a query for a view |
Or | Used within the ‘Where’ element to group filters in a query for a view |
DateRangesOverlap | compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap |
Example for View:
<View>
<Query>
<OrderBy>
<FieldRef Name='ID'/>
</OrderBy>
<Where>
<Or>
<Geq>
<FieldRef Name='Field1'/>
<Value Type='Number'>1500</Value>
</Geq>
<Leq>
<FieldRef Name='Field2'/><Value Type='Number'>500</Value>
</Leq>
</Or>
</Where>
</Query>
<ViewFields>
<FieldRef Name='Title'/>
<FieldRef Name='Name'/>
</ViewFields>
<RowLimit>10</RowLimit>
</View>
Example: DateRangesOverlap
<where>
<DateRangesOverlap>
<FieldRef Name="EventDate"></FieldRef>
<FieldRef Name="EndDate"></FieldRef>
<FieldRef Name="RecurrenceID"></FieldRef>
<Value Type="DateTime">
<Now/>
</Value>
</DateRangesOverlap>
</Where>
Example: Begins With / Contains
<Where>
<And>
<BeginsWith>
<FieldRef Name="Conference"/>
<Value Type="Note">Morning</Value>
</BeginsWith>
<Contains>
<FieldRef Name="Conference" />
<Value Type="Note">discussion session</Value>
</Contains>
</And>
</Where>
Building Queries for working with DateTime Values
Filtering on DateTime fields also requires some extra attention. First of all when querying for a specific date, you have to use the SharePoint datetime notation:
<Where>
<Ge>
<FieldRef Name="StartDate" />
<Value Type="DateTime">2008-08-10T10:00:00Z</Value>
</Ge>
</Where>
But in this case the date is hard coded and the time part will not be taken into account. This query will return all list items with a start date as of 10 August 2008, also those starting before 10 o’clock. If you want your query to take into account the time part, you have to use a special attribute IncludeTimeValue that you can set on the FieldRef element:
<Where>
<Ge>
<FieldRef Name="StartDate" IncludeTimeValue="TRUE" />
<Value Type="DateTime">2008-08-10T10:00:00Z</Value>
</Ge>
</Where>
This query will return all list items with a start date as of 10 August 10 o’clock.
As already said, this way the date is hard coded. If you want your query a bit more dynamic, you can always use the element Today.
<Where>
<Ge>
<FieldRef Name="StartDate" />
<Value Type="DateTime"><Today /></Value>
</Ge>
</Where>
Today will not take a time part into account. You can also add or subtract a number of days from today’s date. In that case you have to add the Offset attribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days.
<Where>
<Ge>
<FieldRef Name="StartDate" />
<Value Type="DateTime"><Today Offset="10" /></Value>
</Ge>
</Where>
Retrieving List Items with CAML using the SharePoint Object Model
If you need to retrieve items from a list when developing web parts, application pages or custom field types you can best use the SPQueryobject from the SharePoint object model. This object is located in the Microsoft.SharePoint namespace of the Microsoft.SharePoint.dll located in the Global Assembly Cache.
Instantiate the object as follows:
SPQuery qry = new SPQuery();
The most important property is the Query property, which needs to be set to your CAML query:
string camlquery = "<OrderBy><FieldRef Name='Country' /></OrderBy><Where>"
+ "<Eq><FieldRef Name='LastName' /><Value Type='Text'>Smith</Value></Eq>"
+ </Where>";
qry.Query = camlquery;
At this point you can execute the query on your list:
SPListItemCollection listItemsCollection = list.GetItems(qry);
A small remark with the GetItems method of the SPList instance: this method returns a collection of type SPListItemCollection. It is possible that it is easier working with a DataTable. In that case you can execute the query as follows:
DataTable listItemsTable = list.GetItems(qry).GetDataTable();
The query will not only return all list items that have their last name set to Smith, but also all columns of each list item. In cases you work with large lists it can be important to retrieve a subset of list items containing only the columns you need. In that case you will have to set the ViewFields property of the SPQuery object. You can do this by specifying all columns you want to see returned:
qry.ViewFields = "<FieldRef Name='FirstName' /><FieldRef Name='LastName' />";
This will return the first name and the last name of the retrieved employees, but also the system fields like the ID and the Created date.
The major disadvantage of the SPQuery object is that you can query only one list. If you want to query more than one list you will have to use the SPSiteDataQuery.
We can also retrieve the List Items from List.asmx (Web Service).
