Click here to Skip to main content
15,867,453 members
Articles / Productivity Apps and Services / Sharepoint / SharePoint 2013

Building CAML for querying List in SharePoint

Rate me:
Please Sign up or sign in to vote.
3.47/5 (6 votes)
19 Oct 2016CPOL4 min read 95.9K   350   6   8
Writing CAML queries in a optimized, efficient and easiest way.

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

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

Client Object Model

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

Javascript Object Model

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

REST API SharePoint 2013

JavaScript
$.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

create view

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

view type

3. Select Columns those you want in CAML Query

column names

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

ordering

This is the equivalent of <OrderBy> Element

XML
<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 where

This equivalent of <Where> Element

XML
<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

null checking

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

XML
<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

group by

This is equivalent to the GroupBy Element

XML
<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.

C++
$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 or fiddler. 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

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

JavaScript
{
    "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. 

#Update

Advanced Rest Client has authentication problem in new version. It does not work seamlessly with SharePoint. So I made my own SP REST Client from Chrome. You can explore/test REST API using it very easily. Documentation of my SP REST Client can be found here.

SP REST Client

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. 

JavaScript
$.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.

JavaScript
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Instructor / Trainer Jashore University of Science and Technology
Bangladesh Bangladesh
2016 Microsoft MVP

Currently, I am devoted to provide technical and development support to the SharePoint clients and also I am working on angularjs. I am experienced with C#, ASP.NET, SharePoint, SignalR, angularjs, MS SQL, Oracle 11g R2, Windows Phone, Firefox OS and so on. I have fallen in love with many technologies but never got married to any of them. I am evolving myself as full stack developer. I always like to share knowledge as much as to gather from you.

Comments and Discussions

 
Questionjson parsing failed Pin
danyseb22-Nov-15 4:29
danyseb22-Nov-15 4:29 
AnswerRe: json parsing failed Pin
Atish Dipongkor22-Nov-15 20:20
professionalAtish Dipongkor22-Nov-15 20:20 
have a look on the header.

headers: {<br />
            "accept": "application/json;odata=verbose",<br />
        }


So error and success are JSON. I can not find any failure issue.
QuestionImage for 5th point Pin
Akhil Mittal22-May-15 2:29
professionalAkhil Mittal22-May-15 2:29 
AnswerRe: Image for 5th point Pin
Atish Dipongkor22-May-15 2:40
professionalAtish Dipongkor22-May-15 2:40 
GeneralRe: Image for 5th point Pin
Atish Dipongkor24-May-15 4:27
professionalAtish Dipongkor24-May-15 4:27 
GeneralRe: Image for 5th point Pin
Akhil Mittal25-May-15 18:35
professionalAkhil Mittal25-May-15 18:35 
QuestionImages? Pin
codewaver22-May-15 1:47
codewaver22-May-15 1:47 
AnswerRe: Images? Pin
Atish Dipongkor22-May-15 1:54
professionalAtish Dipongkor22-May-15 1:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.