Sharepoint – CAML queries and examples

Posted onAuthorHeelpBook


To totally unlock this section you need to Log-in

CAML stands for Collaborative Application Markup Language. It is an XML based language that is used in SharePoint.

CAML query generally used on SharePoint objects like List, Library, List Template, Content-Type etc.

Before learning CAML Query you first need good exposure on SharePoint Lists, Libraries, and other objects. CAML Query is totally different from what we have used till now like Linq, SQL.

We can use CAML Query in SharePoint with Server Side Object model as well as in Client Side Object Model (CSOM). There are tools to generate CAML Query. There are various tools available to do this. Some of the tools to generate CAML Query are U2U, SPCAMLQueryHelper.

CAML query helps to fetch items from SharePoint list, One can use CAML Query to perform the update, delete a record from SharePoint list and library. Apart from that, you may apply various where clause to apply a filter in your CAML Query.

CAML Query usages

We can use CAML Query everywhere like SOM (Sever Object Model), CSOM (Client Object Model), JSOM (Javascript Object Model) and REST API also. Wherever we use it, the syntax is always same.

Sever Side Object Model

var spQuery = new SPQuery();
spQuery.Query = string.Concat("CAML Query goes here");
var listItems = spWeb.Lists["List Name"].GetItems(spQuery);

Client Object Model

CamlQuery query = CamlQuery();
query.ViewXml = string.Concat("CAML Query goes here");
var listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();

Javascript Object Model

var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml('CAML Query goes here');
this.collListItem = oList.getItems(camlQuery);
clientContext.load(collListItem);

REST API SharePoint 2013

$.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getbytitle('List Name')/GetItems",
        type: "POST",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "content-Type": "application/json;odata=verbose"
        },
        data: JSON.stringify({ 
        query : {
          __metadata: {
            type: "SP.CamlQuery" 
          },
          ViewXml: 'CAML Query goes here'
        }
      }),
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });

SharePoint CAML Query Syntax

CAML Query syntax will be a new experience as compared to SQL queries. Everything in CAML Query is within <>. If you want to return specific items from List, your query must start with <Query> tag.

Below CAML Query will return all documents whose content type is My Documents:

<Where><Eq><FieldRef Name='Content Type'/><Value Type='Text'>My Documents</Value></Eq></Where>

Some Examples

Get results from SharePoint List where Title is not equal to abc:

<query>
   <where>
      <neq>
         <fieldref Name='Title'>
         <value Type='Text'>abc
      </neq>
   </where>
</query>

Get results from SharePoint List where Title begins with abc:

<query>
   <where>
      <beginswith>
         <fieldref Name='Title'>
         <value Type='Text'>abc
      </beginswith>
   </where>
</query>

Get results from SharePoint List where Title contains abc:

<query>
   <where>
      <contains>
         <fieldref Name='Title'>
         <value Type='Text'>abc
      </contains>
   </where>
</query>

Get results from SharePoint List where Id Lower than or equal to 20:

 <query>
   <where>
      <leq>
         <fieldref Name='ID'>
         <value Type='Counter'>20
      </leq>
   </where>
</query>

Get results from SharePoint List where Title is not null:

<query>
   <where>
      <isnotnull>
         <fieldref Name='Title'>
      </isnotnull>
   </where>
</query>

CAML Query to Limit Row:

<query>
 <view>
 <rowlimit>10
 </view>
</query>

Now let's demonstrate CAML Query with real time SharePoint list.

Fetch data from List using CAML Query where AuthorName = 'Author1':

<query>
<where>
<eq>
<fieldref Name="AuthorName"></fieldref>
<value Type="Text">Author1</value>
</eq>
</where>
</query>

Fetch data from List using CAML Query where Book Release date is lower than a given date:

<query>
<where>
<lt>
<fieldref Name="ReleaseDate"></fieldref>
<value IncludeTimeValue="TRUE" Type="DateTime">2016-08-04T15:08:15Z</value>
</lt>
</where>
</query>

Fetch data from List using CAML Query where stock is available:

<query>
<where>
<eq>
<fieldref Name="stock"></fieldref>
<value Type="Choice">Yes</value>
</eq>
</where>
</query>

Fetch Data using CAML Query where price range is 500 to 800:

<query>
<where>
<and>
<leq>
<fieldref Name="price"></fieldref>
<value Type="Number">800</value>
</leq>
<geq>
<fieldref Name="price"></fieldref>
<value Type="Number">500</value>
</geq>
</and>
</where>
</query>

Fetch data using CAML Query where date range is 04 Jan 2015 to 4 Aug 2016:

<query><where><leq>
<fieldref Name="ReleaseDate"><value IncludeTimeValue="TRUE" Type="DateTime">2016-08-04T15:08:15Z</value>
</leq><geq><fieldref Name="ReleaseDate"></fieldref><value IncludeTimeValue="TRUE" Type="DateTime">2015-01-04T15:18:46Z</geq>
</and></where></query>

Fetch data using CAML Query where Author name = 'Author2' and Publisher Not equal to 'Publisher1':

<query><where><and><eq>
<fieldref Name="AuthorName"><value Type="Text">Author2
<fieldref Name="PublisherName"><value Type="Text">Publisher1</neq></and></where></query>

Fetch data using CAML Query where Author name = 'Author2' and Publisher Not equal to 'Publisher1' and stock is available:

<query><where><and><eq><fieldref Name="AuthorName"><value Type="Text">Author2<and>
<neq><fieldref Name="PublisherName"></fieldref><value Type="Text">Publisher1<eq>
<fieldref Name="stock"><value Type="Choice">Yes</Value></Eq></And></And></Where></Query>

CAML Building in Action

We will just create a list view and get our CAML Query from it. Lets see it in action.

Go to the list settings and click Create View:

Sharepoint - CAML queries and examples

Select Standard View, type View Name and choose Public View:

Sharepoint - CAML queries and examples

Select Columns those you want in CAML Query:

Sharepoint - CAML queries and examples

Select Column Name and choose ascending or descending option for Ordering from Sort section:

Sharepoint - CAML queries and examples

This is the equivalent of <OrderBy> Element:

<OrderBy>
  <FieldRef Name="Field Name"/>
  <FieldRef Name="Field Name" Ascending="FALSE"/>
  <FieldRef Name="Field Name" Ascending="TRUE"/>
</OrderBy>

Now select Column Name, Contition and choose Value from Filter section:

Sharepoint - CAML queries and examples

This equivalent of <Where> element:

<Query>
  <Where>
    <Geq>
      <FieldRef Name="Field Name"/>
      <Value Type="DateTime">
        <Today/>
      </Value>
    </Geq>
  </Where>
  <OrderBy>
    <FieldRef Name="Field Name"/>
  </OrderBy>
</Query>

Let's see some equivalent conditions between View and CAML:

  • is equal to: <Eq>
  • is not equal to: <Neq>
  • is greater than: <Gt>
  • is less than: <Lt>
  • is greater than or equal to: <Geq>
  • is less than or equal to: <Leq>
  • begins with: <BeginsWith>
  • contains: <Contains>

For checking null just keep the value as blank:

Sharepoint - CAML queries and examples

This is equivalent to <isnull> and <IsNotNull> element:

<Or>
<IsNull><FieldRef Name="Employee" /></IsNull>
<IsNotNull><FieldRef Name="ID" /></IsNotNull>
</Or>

Now you can go for Group By if it is needed from Group By section:

Sharepoint - CAML queries and examples

This is equivalent to the GroupBy element:

<GroupBy Collapse="TRUE" GroupLimit="30">
<FieldRef Name="Title" />
</GroupBy>

Obtaining CAML Query from view

CAML query can be obtained from view in several ways. Let's start with PowerShell.

Open PowerShell and paste following code and that will yield our CAML query in a text file.

$spWeb = Get-SPWeb -Identity "Site URL goes here";
$spList = $spWeb.Lists["List Name"];
$spView = $spList.Views["View Name"];
$spView.Query | Out-File "Path with file name"

Now open the text file and use it in SOM, CSOM, JSOM or REST API.

Now, if you are not comfortable with PowerShell, you can use any rest client like Fiddler. Below example will give some general instructions on Fiddler. This example is applicable for SharePoint 2013.

API endpoint for Sharepoint is usually: http://Site URL/_api/Web/Lists/getbytitle('List Name')/Views/getbytitle('View Name')?$select=ViewQuery.

The Headers to send will be: Accept: application/json;odata=verbose, to get result as JSON. Accept header actually shows what exactly you need in your response when you get data back from the server.

On Fiddler, we first need to go on Composer tab, enter our URL, like http://Site URL/_api/Web/Lists/getbytitle('List Name')/Views/getbytitle('View Name')?$select=ViewQuery, and then insert, as Get into Parser tab, the Accept: application/json;odata=verbose text, to get also the JSON data for this request. Now click on Execute button and that will return the result as JSON (selecting the JSON section after the request has been done by Fiddler).

{
    "d": {
        "__metadata": {
            "id": "http://site url/_api/Web/Lists(guid'ec945846-bea2-4d3d-ba02-6e9f6dea9541')/Views(guid'91eb23c1-b489-4eb6-9f9a-571c32db6a4f')",
            "uri": "http://site url/_api/Web/Lists(guid'ec945846-bea2-4d3d-ba02-6e9f6dea9541')/Views(guid'91eb23c1-b489-4eb6-9f9a-571c32db6a4f')",
            "type": "SP.View"
        },
        "ViewQuery": ""
    }
}

Just copy the value of ViewQuery from returned JSON and use it.

The following are two example, in Javascript Object Model (JSOM), for Sharepoint, to query records from Sharepoint Lists.

Get List Items by CAML Query

Use the set_viewXml method of the SP.CamlQuery object to specify a CAML query to retrieve items.

SP.SOD.executeOrDelayUntilScriptLoaded(showListItems,"core.js");

function showListItems(){
    var clientContext = new SP.ClientContext();
    var list = clientContext.get_web().get_lists().getByTitle("List Title");
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(
        "" +
            "" +
                "Value" + 
            "" +
            "" +
        ""+
        //"5000" +
        "");
    var items = list.getItems(camlQuery);
    clientContext.load(items);
    clientContext.executeQueryAsync(function(){
        var itemArray = [];
        var itemEnumerator = items.getEnumerator();
        while(itemEnumerator.moveNext()){
            var item = itemEnumerator.get_current();
            var id = item.get_item("ID");
            var title = item.get_item("Title");
            itemArray.push(id + ": " + title);
        }
        alert("ID: Title\n"+itemArray.join("\n"));
    },function(sender,args){alert(args.get_message());});
}

Paging the results of a CAML query

You can take advantage of the RowLimit element in a CAML query to retrieve only a subset of results with each query.

Use the get_listItemCollectionPosition method of a list item collection to retrieve the current position, then use that value as the parameter in an SP.CamlQuery object's set_listItemCollectionPosition method to retrieve the next batch of results.

SP.SOD.executeOrDelayUntilScriptLoaded(showListItems,"sp.js");

function showListItems(){
    var itemArray = [];
    var clientContext = new SP.ClientContext();
    var list = clientContext.get_web().get_lists().getByTitle("List Title");
    var viewXml = 
        "" +
            "" +
        ""+
          "1" +
        "";
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(viewXml);            
    var items = list.getItems(camlQuery);
    clientContext.load(items);
    clientContext.executeQueryAsync(loadResults,showError);

    function loadResults(){
        var resultsFound = false;            
        var itemEnumerator = items.getEnumerator();
        while(itemEnumerator.moveNext()){
            var item = itemEnumerator.get_current();
            var id = item.get_item("ID");
            var title = item.get_item("Title");
            itemArray.push(id + ": " + title);
        }
        var pos = items.get_listItemCollectionPosition();
        if(pos !== null){ 
            if(confirm("Results so far: \nID: Title\n"+itemArray.join("\n"))){
                camlQuery = new SP.CamlQuery();
                camlQuery.set_listItemCollectionPosition(pos);
                camlQuery.set_viewXml(viewXml);            
                items = list.getItems(camlQuery);
                clientContext.load(items);
                clientContext.executeQueryAsync(loadResults,showError);
            }
        }else{
            alert("Total Results: \nID: Title\n"+itemArray.join("\n"));
        }
    }
    function showError(sender,args){
        alert(args.get_message());
    }
}

Source
Go to source!

Language
English