Home
CAML (Collaborative Application Markup Language) is an XML-based query language that helps you querying lists and libraries in any version of SharePoint since SharePoint 2003. This tool offers you following functionality:
- you can build CAML queries for single lists and document libraries
- beside the pure CAML queries, you can also get code snippets for the server-side object model, the .NET client-side object model, the JavaScript client-side object model and last but not least code snippets when working with REST.
You can connect to SharePoint Online, SharePoint 2019, SharePoint 2016 and SharePoint 2013 through the Client Object model. For the older versions of SharePoint you have to connect through the Web Services of SharePoint.
This is the start screen:
Click on the connection button in the right upper corner of the form:
This will open the connection form.
Fill out the URL to your SharePoint site.
If you choose SharePoint 2019, SharePoint 2016, SharePoint 2013 or SharePoint Online, you will connect through the Client Object model. If you choose an older version of SharePoint you will connect through the Web Services of SharePoint. You can choose to connect with your current credentials, or with another username and password.
If you click the O365 button, you will be redirected to log on via web login. This makes it possible to log on using MFA.
When you enter a URL, a small pre-check is done. If you entered a wrong URL, meaning a URL that can’t be found by the CAML Designer, you get following error message:
If you enter credentials that cannot be accepted by the SharePoint site you want to access, you get the following error message:
Click the connect button to connect the Caml Designer to your SharePoint site. All lists and libraries will be listed at left side of the form.
The CAML Designer stores recent connections. You can clear the history by clicking the "Settings" button:
and then click the "Clear History" button:
Expand the treeview to see all the lists:
The right pane is divided in two parts: an upper part where you can construct your query and a lower part where you can view the CAML query. You can also view code snippets for the different object models with which you can query SharePoint lists.
To construct your query, you can toggle the tabs in the upper panel:
- ViewFields: toggle this button if you want to define the columns that you want to be returned in your result set (it corresponds to the SELECT clause of a SQL query).
- Where: toggle this button if you want to define one or more filters.
- OrderBy: toggle this button if you want your result set to be sorted.
- Query Options: toggle this button if you want to set additional query options. To be able to construct a CAML query, you have to select a list from the treeview. Each tab contains a panel that gets populated with available fields from the selected list.
Each clause will be documented in detail in the following sections, and how you can use the CAML Designer to build up each of these clauses.
The lower panel contains tabs to list the different code snippets based on the CAML query that is constructed in the upper panel. Following code snippets are provided:
- The Caml tab shows you the pure CAML query.
- The Server OM tab will show you a code snippet using the server-side object model to execute your CAML query.
- The CSOM .NET tab will contain a code snippet that you can use when developing f.e. a WPF application that needs to connect to a SharePoint site.
- The CSOM Rest tab will contain a code snippet that retrieves data from SharePoint using REST.
- The web services tab will display a code snippet that executes the CAML query using the lists.asmx web service.
- The PowerShell tab will display a code snippet that executes the CAML query using PowerShell.
No other types of code snippets are available yet. Code snippets are only provided in C#.
Hidden fields
In some cases you want to build a filter based on a value in a hidden field. By default the CAML Designer doesn’t show hidden fields. To solve this issue we added the check box “Show hidden fields” to the user interface just above the list treeview. By default the hidden fields are not displayed, but you can click this check box to get them displayed.
One of our users als reported that there were issues with long display names. It would ask a lot of redesign to have this properly displayed so we chose to add a tooltip that shows the complete display name. We hope that this suits your needs.
If you need a sorted result set, you have to define a sort order. To define a sort order for your query, you have to click the Order By tab in the upper panel.
Click the field in the left panel on which you want to sort and drag it to the right of the panel. The field will appear in the right panel with an image indicating the sort order. Just click the image if you want to change the sort order.
While you are building the sort order by clicking around, you will see your CAML query evoluate at the bottom of the screen. When in first instance the Last Name field is added, the CAML looks like the following:
<OrderBy> <FieldRef Name='Title' /> </OrderBy>
While the available list fields are displayed with their display name, the internal name of the field is used to build the OrderBy clause.
If no sort order is specified, the result set will be ordered in ascending order.
Select a second field from the left panel if you want to sort on more than one field. Your CAML query will immediately change as follows:
<OrderBy>
<FieldRef Name='Title' />
<FieldRef Name='Company' />
</OrderBy>
If you want to sort in descending order, click on the image next to the field name in the right panel and your CAML will look like this:
<OrderBy>
<FieldRef Name='Title' />
<FieldRef Name='Company' Ascending='FALSE'/>
</OrderBy>
If you want to remove a field from the order by clause, drag it back to the left panel.
You can also change the order of the selected fields, you could for example drag and drop the Company tile to the top of the list; the CAML snippets will automatically be updated.
The query is not constantly executed whenever you click somewhere to show you the results of the query you are building. If you want to see the result of your query, you can click the Execute button at the top right corner of the screen:
Or click the Test tab next to the Query Options tab:
Your results will be displayed in a grid:
The Test tab displays also a label that indicates the number of rows that are returned by the result set. Once the number becomes visible, you know that the retrieval has finished. This is handy when your query does not return a result and no grid becomes visible. The number zero will indicate that the query is finished but that no results have been found.
In the bottom pane you can inspect the CAML query and the different code snippets. By default the CAML tab is activated. Click the other tabs to inspect the different code snippets.
For REST snippets, you can choose if the results must be returned in JSON format or in ATOM format:
The difference lays in the way the results are returned in the response. If you sent your request for json, the results are returned as follows:
If you sent your request for atom, the results are returned as follows:
If you need to pass your CAML query throught the plain old lists.asmx web service, you can use the following code snippet:
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);
When you execute a query, the result set contains by default all the fields from the default view, plus a number of system columns like ID, Created, and Modified.
If you need only a limited set of columns returned in your result set, you will have to define a ViewFields clause.
If you want to remove a field from the ViewFields clause, drag it back to the left panel.
You can also change the order of the selected fields, you could for example drag and drop the Job Title tile to the top of the list, it will automatically change the CAML snippets.
Your CAML query looks like the following:
<ViewFields>
<FieldRef Name='Title' />
<FieldRef Name='FirstName' />
<FieldRef Name='Company' />
</ViewFields>
To avoid the retrieval of all the list items in a list or document library, you can filter data by defining a Where clause. Constructing a Where clause in CAML can be rather complex, especially in cases where you need more than one filter.
Click the Where tab on the upper panel and drag a field from the left panel to the right panel. The field control now consists of 3 parts: the field name, an operator button and a control in which you can specify a value.
CAML contains following operators:
- Eq: equal
- Geq: greater than or equal
- Gt: greatoer then
- Leq: lower than or equal
- Lt: lower than
- BeginsWith
- Contains
- IsNull
- IsNotNull
- Includes (new in SharePoint 2010)
- In (new in Sharepoint 2010)
Right-click the operator button if you want to change the operator.
Executing a query is not only about CAML. When working with the SPQuery object in the server model, you can set different properties to influence the returned list items. When working with the SharePoint web services, these options are translated into CAML and are part of the QueryOptions element.
Remark: Not all query options work with REST snippets. I’ll mention each time what works and what not.
When specifying a ViewFields clause, only values for these fields are returned, together with a few system columns like ID, Created and Modified. You can also indicate that you want to have the required fields returned too in the resultset. You can do this by setting the IncludeMandatoryColumns to true.
In the CAML panel you will see an additional node.
But in the server object model, this information must be passed by setting the IncludeMandatoryColumns property to true:
SPList spList = spWeb.Lists.TryGetList("Tests");
if (spList != null)
{
SPQuery qry = new SPQuery();
qry.Query = @"
<Where>
<Lt>
<FieldRef Name='StartDate' />
<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value>
</Lt>
</Where>";
qry.ViewFields = "<FieldRef Name="Title" /><FieldRef Name="StartDate" /><FieldRef Name="Countries" />";
qry.IncludeMandatoryColumns = true;
SPListItemCollection listItems = spList.GetItems(qry);
}
If you need to use the lists.asmx web service to execute your CAML query, you have to pass a QueryOptions node:
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<Where><Lt><FieldRef Name='StartDate' />"
+ "<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value></Lt></Where>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='StartDate' /><FieldRef Name='Countries' />";
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<IncludeMandatoryColumns>True</IncludeMandatoryColumns>";
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);
Remark: this options doesn’t seem to work with the Client Object model and with REST.
Another query option is the row limit. It can be used to limit the number of rows returned in the result set.
There are also a number of query options for working with files and folders in a document library. A folder is a special list item on a list or document library. If you execute a standard CAML query you will end up with all fiels and folders from the root folder. In my case, this is the content of the root folder of my Shared Documents library:
The user interface of the CAML Designer gives you a wide range of options that you can configure.
To get files and folders from the root and its sub folders with CAML, you can set the ViewAttributes property. This property is not translated in REST because a standard REST request already returns files and folders from the whole folder structure of the queried document library. As this property works as before, I refer to my article CAML Designer for SharePoint 2010 for more details.
But you can also query a sub folder of a document library. The CAML Designer offers an option where you can fill out the folder on which you want to query:
You can also specify if you want to return:
- files and folders
- only files
- only folders
Every setting has an impact on the CAML query.