Building CAML for querying List in SharePoint

Introduction

Writing CAML by hand (typing) is always supposed to be painful. There are many tools available in online for doing this though doveloper must type it by his/her own. Tools just help to select query operators and debug. There is no drag and drop option or UI for building whole query. In this article, I will show how we can build CAML Query without typing. I can assure you that it must be optimized than typing by own. Target audiences those who are already familiar with CAML and creating list view.

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. Let's recall our code..

Sever Side Object Model

Hide Copy Code

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

Client Object Model

Hide Copy Code

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

Javascript Object Model

Hide Copy Code

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

REST API SharePoint 2013

Hide Copy Code

$.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));
        }
    });

Now we will see how we can build our CAML query without typing by hands.

CAML Building in Action

The approach is very straight forward. We will just create a list view and get our CAML Query from it. Lets see it in action.

1. Go to the list settings and click Create View

2. Select Standard View, type View Name and choose Public View

3. Select Columns those you want in CAML Query

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

This is the equivalent of <OrderBy> Element

Hide Copy Code

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

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

This equivalent of <Where> Element

Hide Copy Code

<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

Condition	Equivalent CAML Query
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

This is equivalent to <IsNull> and <IsNotNull> Element

Hide Copy Code

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

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

This is equivalent to the GroupBy Element

Hide Copy Code

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

Obtaining CAML Query from view

CAML query can be obtained from view in several ways. My preferred way is PowerShell. So let's start with PowerShell. Open PowerShell and paste following scripts and that will yield our CAML query in a text file.

Hide Copy Code

$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

If you are not comfortable with PowerShell, you can use any rest client like Advanced Rest Client for Chrome orfiddler. Below example demonostrate Advanced Rest Client. This example is applicable for SharePoint 2013.

API endpoint is

http://Site URL/_api/Web/Lists/getbytitle('List Name')/Views/getbytitle('View Name')?$select=ViewQuery

Headers will be Accept: application/json;odata=verbose to get result as JSON

Now click on Send button and that will return the result as JSON like following

Hide Copy Code

{
    "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": "<GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"Title\" /></GroupBy><OrderBy><FieldRef Name=\"ID\" /></OrderBy><Where><Or><IsNull><FieldRef Name=\"Employee\" /></IsNull><IsNotNull><FieldRef Name=\"ID\" /></IsNotNull></Or></Where>"
    }
}

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

The CAML query can be obtained via REST API and jQuery in SharePoint 2013. You can go through my another article about REST API here. Paste following code in the console of your browser if jQuery is included in your site.

Hide Copy Code

$.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getbytitle('list name')/Views/getbytitle('view name')?$select=ViewQuery",
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function (data) {
            console.log(data.d);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });

For SharePoint 2010, following method will help to get CAML query from view.

Hide Copy Code

function getCamlQueryFromView(listTitle, viewTitle) {
    var context = new window.SP.ClientContext.get_current();
    var list = context.get_web().get_lists().getByTitle(listTitle);
    var view = list.get_views().getByTitle(viewTitle);
    context.load(view);
    context.executeQueryAsync(
        function(sender, args) {
            console.log(view.get_viewQuery())
        },
        function(sender, args) {
            alert("error: " + args.get_message());
        }
    );
}

Above code will work for SharePoint 2013 also. As approach varies from SharePoint version to version, I always preferred to use PowerShell.

So that's all and start building CAML in this way and let me know your feedback how it works!

Points of Interest

I must acknowledge that something I could not find equivalent in this way. If you can achieve it, please let me know in comment thereafter I will update my article. Some of those are mentioned below.

1. Membership Element

2. NotIncludes Element

3. In Element

4. DateRangesOverlap Element

5. and others

If you need above elements for complex CAML query, you have to type it by hand. My suggestion is: at first start with my approach, then modify it accordingly. I hope it will releive your 80% work.

Friday, August 28, 2015