CAML Query tutorial for SharePoint
- CAML - Collaborative Application Markup Language
- XML- Extensible Markup Language based query language
CAML: 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> <Query>
<Eq>
<FieldRef Name=”FieldName” />
<Value Type=”DataType”>Value</Value>
</Eq>
</Where>
<OrderBy>
<FieldRef Name=”FieldName” />
<FieldRef Name=”FieldName” />
</OrderBy>
</Query>Refer: Query Schema Elements
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
Comparison Operators
Comparison Operators
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>
<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>
CAML Query
- A caml query is build in two parts.
- 1) Sort
- 2) Filter
- Sort part is the sorting based on some columns
- Filter part is the where clause of the CAML query
- In order to build up the XML for the CAML we create the where clause as follows:
- Write the where clause just as you write in sql
- Find out the operators such as =, <=, <,> etc.
- Find out the keywords for these operators in CAML language. For example = is eq, < is lt and so on.
- Break the sql string such that the operator comes first and then comes the operands. Continue this till the whole string is completed in this fashion.
- Replace the operators with the keywords in CAML language.
- Example: where column1=”a” and column2=”b” and column3 like ‘%c%’
- => where (column1=”a” and column2=”b”) and column3 like ‘%c%’
- => where and (column1=”a” and column2=”b”) (column3 like ‘%c%’)
- => where and (and (column1=”a” column2=”b”) column3 like ‘%c%’)
- => where and and (= column1 a column2 b) contains column3 c
- => where and and eq column1 a eq column2 b contains column3 c
- => <where>
<and>
<and>
<eq>
<FieldRef name=”Column1” />
<Value Type=string>a</Value>
</eq>
<eq>
<FieldRef name=”Column2” />
<Value Type=string>b</Value>
</eq>
</and>
<contains>
<FieldRef name=”Column3” />
<Value Type=string>c</Value>
</contains>
</and>
</where>
Retrieving List Items with CAML Query - Examples
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 theMicrosoft.SharePoint.dll located in the Global Assembly Cache.
Instantiate the object as follows:
SPQuery query = 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>";
query.Query = camlquery;
At this point you can execute the query on your list:SPListItemCollection listItemsCollection = list.GetItems(qry);
Get data in datatable
A small remark with the GetItems method of the SPList instance: this method returns a collection of typeSPListItemCollection. 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();
View fields property
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' />";
query.ViewFieldsOnly = true;
This will return the first name and the last name of the retrieved employees, but also the system fields like the ID and theCreated 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. More on this object in a later article because it earns special attention.
It is common knowledge by now but let me remind you that it’s always a good idea to use SPQuery to retrieve a subset of list items. You can loop through the list item collection to find the list items that match your needs but this will have a serious negative impact on the performance of your work.
IncludeMandatoryColumns
When this Boolean property is set to True, the result set will not only return the columns as defined in the ViewFieldsproperty, but also the columns that you defined in the list as required.When working with the SPQuery object:
qry.IncludeMandatoryColumns = true;
When working with the GetListItems method of the Lists.asmx web service:XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml =
"<IncludeMandatoryColumns>True</IncludeMandatoryColumns>";
RowLimit
Setting this property limits the number of rows returned in the result set.When working with the SPQuery object:
qry.RowLimit = 10;
Example:
using (SPSite site = new SPSite("http://localhost"))
{
using (SPWeb web = site.OpenWeb())
{
SPList List = oWebsiteRoot.Lists["Tasks"];
// Build a query.
SPQuery query = new SPQuery();
Query.Query = "<Where><Eq><FieldRef Name='Status'/>" +
"<Value Type='Text'>Completed</Value></Eq></Where>";
query.ViewFields = string.Concat(
"<FieldRef Name='AssignedTo' />",
"<FieldRef Name='LinkTitle' />",
"<FieldRef Name='DueDate' />",
"<FieldRef Name='Priority' />");
query.ViewFieldsOnly = true;
query.RowLimit = 10;
SPListItemCollection collListItems = List.GetItems(query);
DataTable listItemsTable = List.GetItems(query).GetDataTable();
}
}
Another Example - Using Sharepoint Client Object Model:
Four properties of ListItem are not available by default when you return list items:
DisplayName, EffectiveBasePermissions, HasUniqueRoleAssignments, and RoleAssignments
Use Include method to get them.
Only the specified properties are available after query execution.
Therefore, you receive a PropertyOrFieldNotInitializedException
if you attempt to access other properties beyond those that have been specified.
In addition, you receive this error if you attempt to use properties such as
ContentType or ParentList to access the properties of containing objects.
using System;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
namespace Microsoft.SDK.SharePointServices.Samples
{
class RetrieveListItems
{
static void Main()
{
string siteUrl = "http://MyServer/sites/MySiteCollection";
ClientContext clientContext = new ClientContext(siteUrl);
SP.List oList = clientContext.Web.Lists.GetByTitle("Announcements");
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><Query><Where><Geq><FieldRef Name='ID'/>" +
"<Value Type='Number'>10</Value></Geq></Where></Query><RowLimit>100</RowLimit></View>";
ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem);
OR
clientContext.Load(collListItem,
items => items.Include(
item => item.Id,
item => item.DisplayName,
item => item.HasUniqueRoleAssignments));
clientContext.ExecuteQuery();
foreach (ListItem oListItem in collListItem)
{
Console.WriteLine("ID: {0} \nTitle: {1} \nBody: {2}", oListItem.Id, oListItem["Title"], oListItem["Body"]);
}
}
}
}
Retrieving specific fields from a specified number of items
The following example shows how to retrieve specific fields from only the first five items in a list. Because only the Title and Body columns are specified, these are the only properties that are available.
using System;
using System.Linq;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
namespace Microsoft.SDK.SharePointServices.Samples
{
class RetrieveSpecificItemsFields
{
static void Main()
{
string siteUrl = "http://MyServer/sites/MySiteCollection";
ClientContext clientContext = new ClientContext(siteUrl);
SP.List oList = clientContext.Web.Lists.GetByTitle("Announcements");
CamlQuery camlQuery = new CamlQuery();
ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(
collListItem,
items => items.Take(5).Include(
item => item["Title"],
item => item["Body"]));
clientContext.ExecuteQuery();
foreach (ListItem oListItem in collListItem)
{
Console.WriteLine("Title: {0} \nBody: {1}\n", oListItem["Title"], oListItem["Body"]);
}
}
}
}
Retrieving items from all the lists in a Web site
using System;
using System.Linq;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
namespace Microsoft.SDK.SharePointServices.Samples
{
class RetrieveFirstTenItemsAllLists
{
static void Main()
{
string siteUrl = "http://MyServer/sites/MySiteCollection";
ClientContext clientContext = new ClientContext(siteUrl);
ListCollection collList = clientContext.Web.Lists;
clientContext.Load(
collList,
lists => lists.Where(
list => list.Hidden == false).Include(
list => list.Title,
list => list.Items.Take(10)));
clientContext.ExecuteQuery();
foreach (SP.List oList in clientContext.Web.Lists)
{
string listTitle = oList.Title;
int itemCount = oList.Items.Count;
Console.WriteLine("List {0} returned with {1} items", listTitle, itemCount);
}
}
}
}
