How to learn CAML Query in SharePoint 2010 and 2013 - A Complete tutorial guide

CAML Query is one of the most important topics in SharePoint. CAML Query is the way for querying items from SharePoint objects like List and Library. This blog is helpful in SharePoint 2010 development as well as in SharePoint 2013 development.

CAML Query tutorial for SharePoint 2010 and SharePoint 2013

In this blog, I will explain -   
  • Introduction to CAML Query. 
  • Where we can use CAML Query? 
  • CAML Query Syntax and How to Write CAML Query 
  • Operators in CAML Query 
  • How to use CAML Query builder? 
  • How to use CAML Query in real-time development in SharePoint 2010? 
  • Commonly used CAML Query for SharePoint developer

What is CAML Query?

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.

Where we can use CAML Query?

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.

How to write CAML Query?

Writing CAML Query by own gives extra pain as CAML Query is not similar to structure language that we have used. CAML Query has its own sections to write. Each section has its own meaning in CAML Query. Also, CAML Query does not show error on runtime. To overcome this we have various tools to generate Query. We will see CAML Query tool later in this blog.

Learn CAML Query Step by Step

CAML Query implementation in C# code with SharePoint

Below is the code to fetch items from SharePoint Announcement List using CAML Query.

CAML Query with Client Object Model:

    class Program
    {
        static void Main(string[] args)
        {
            string siteUrl = "http://myserver:1985";

            ClientContext clientContext = new ClientContext(siteUrl);
            SP.List oList = clientContext.Web.Lists.GetByTitle("Announcements");

            CamlQuery camlQuery = new CamlQuery();
            camlQuery.ViewXml = "<View><RowLimit>100</RowLimit></View>";

            ListItemCollection collListItem = oList.GetItems(camlQuery);

            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} \nDisplay name: {1} \nUnique role assignments: {2}",
                    oListItem.Id, oListItem.DisplayName, oListItem.HasUniqueRoleAssignments);
            }
        }
    }


Nested CAML Query Syntax:


<Where>
           <And>
                <Or>
                     <And>
                          <Eq>
                               <FieldRef Name="Name" />
                               <Value Type="Text">Mick</Value>
                          </Eq>
                          <Eq>
                               <FieldRef Name="City" />
                               <Value Type="Text">Delhi</Value>
                          </Eq>
                     </And>
                </Or>
                <Eq>
                     <FieldRef Name="Valid" />
                     <Value Type="Boolean">1</Value>
                </Eq>
                <IsNull>
                     <FieldRef Name="JoinDate" />
                </IsNull>
           </And>

      </Where>


Operators in SharePoint CAML Query:

Logical Operator - AND, OR
Comparison Operator

Comparison Operators in CAML Query:

Comparison Operators
General Meaning
Eq
=
Gt
Lt
Geq
>=
Leq
<=
Neq
<> 
Contains
Like
IsNull
Null
IsNotNull
NotNull
BeginsWith
Beginning with word
DateRangesOverlap
compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap

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>

SharePoint CAML Query for Lookup column



<Query>
   <Where>
      <And>
         <Eq>
            <FieldRef Name='Title' />
            <Value Type='Text'>ABC</Value>
         </Eq>
         <And>
            <Eq>
               <FieldRef Name='city' />
               <Value Type='Lookup'>Delhi</Value>
            </Eq>
            <Eq>
               <FieldRef Name='IsActive' />
               <Value Type='Boolean'>1</Value>
            </Eq>
         </And>
      </And>
   </Where>
</Query>

Above Query will select record where Title = ABC, City = Delhi and IsActive = 1 (True)

SharePoint CAML Query Example

Get results from SharePoint List where Id > 1

<Query>
   <Where>
      <Gt>
         <FieldRef Name='ID' />
         <Value Type='Counter'>1</Value>
      </Gt>
   </Where>
</Query>

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


<Query>
   <Where>
      <Neq>
         <FieldRef Name='Title' />
         <Value Type='Text'>abc</Value>
      </Neq>
   </Where>
</Query>

Get results from SharePoint List where Title begins with abc

<Query>
   <Where>
      <BeginsWith>
         <FieldRef Name='Title' />
         <Value Type='Text'>abc</Value>
      </BeginsWith>
   </Where>
</Query>

Get results from SharePoint List where Title contains abc

<Query>
   <Where>
      <Contains>
         <FieldRef Name='Title' />
         <Value Type='Text'>abc</Value>
      </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</Value>
      </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</RowLimit>
</View>
</Query>

Sections used in CAML Query :

<QUERY> :

<VIEW> :

<WHERE> :

<ROWLIMIT> :

<GROUPBY> :

<ORDERBY> :

How to use U2U CAML Query builder tool :

Learn CAML Query using CAML Query Builder Tool

There are tools available in market to generate CAML Query to make developer's life easy and convenient.
To implement CAML Query with U2U CAML Query builder please follow below steps.

1. Create a list in SharePoint 2010, with below data. List name is Book.SharePoint List

2. Open U2U CAML Query builder
Caml Query builder

3. Right click on list.
Caml Query builder

4. Now build your query
Caml Query builder


Caml Query builder


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" /><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" /><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" /><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" /><Value Type="Number">800</Value></Leq><Geq><FieldRef Name="price" /><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><And><Leq><FieldRef Name="ReleaseDate" /><Value IncludeTimeValue="TRUE" Type="DateTime">2016-08-04T15:08:15Z</Value></Leq><Geq><FieldRef Name="ReleaseDate" /><Value IncludeTimeValue="TRUE" Type="DateTime">2015-01-04T15:18:46Z</Value></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</Value></Eq><Neq><FieldRef Name="PublisherName" /><Value Type="Text">Publisher1</Value></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</Value></Eq><And><Neq><FieldRef Name="PublisherName" /><Value Type="Text">Publisher1</Value></Neq><Eq><FieldRef Name="stock" /><Value Type="Choice">Yes</Value></Eq></And></And></Where></Query>



If you found this blog interesting, please comment and subscribe to the RSS feed.

You may read some of the popular blogs:

C# 6.0 new features
What is Cloud Computing?
What is DevOps?
What is Azure Cloud service?
SharePoint Interview Questions and Answers
SharePoint 2013 Server Object Model
Protected by Copyscape

Comments

  1. Replies
    1. Hi Rakesh,
      I am working on SharePoint 2010. I think it should work on SharePoint 2013 any way i will check it on 2013 environment and will let you know.

      Delete
  2. Great blog on CAML Query.

    ReplyDelete
  3. How can I create a List template using CAML query. Please provide a solution for this.
    Any way nice blog on CAML query topic.

    ReplyDelete
  4. Hey ! Nice blog.
    CAML query is very important topic in SharePoint.

    ReplyDelete
    Replies
    1. Thanks for your valuable comment on my blogs - CAML Query in SharePoint 2010

      Delete
  5. CAML Query explained in well manner.
    Good post on CAML query.

    ReplyDelete
    Replies
    1. Thank you so much.
      I will write more CAML query when ever require.

      Delete
  6. CAML query post is very helpful for fresher and experience developer.

    ReplyDelete
  7. Good post on CAML query.

    ReplyDelete
  8. Your blog on CAML query is the best. Myself as a fresher developer in SharePoint got benefit from your blog.
    Keep blogging. Nice post on CAML Query.

    ReplyDelete
  9. Even though SharePoint 2010 is older version. However, CAML query is very important in SharePoint 2010 in development and interview context.
    I think this is very helpful blog for someone who is using CAML query and working on SharePoint 2010.

    ReplyDelete
  10. This blog on "CAML Query in SharePoint" is the best to learn and write query in SharePoint 2010.

    ReplyDelete
  11. Very nice post on CAML query. Now i think i am good enough to understand caml query after reading this blog. Also I will appreciate if you will post content related to SharePoint 2013
    Thanks.

    ReplyDelete
  12. I really appreciate this post.
    This CAML query blog is very nice.
    Keep posting.... :)

    ReplyDelete

Post a Comment

Dear Readers, Please post your valuable feedback in the comment section if you like this blog or if you have any suggestions. I would love to hear the same from you. Thanks

Popular

C# 6.0 New Features

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX

What is Cloud Computing - A guide for beginners