CAML Query Tips, Tricks and Syntax

by
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 2013 and SharePoint 2010

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

CAML Query SharePoint – 

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 SharePoint CAML Query Step by Step

CAML Query example with C# code in SharePoint

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

CAML Query example with SharePoint Client Object Model:

We can use Client Object in SharePoint to get list items using CAML Query.
 
    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> :

You may use CAML query with SPQuery and SPSiteDataQuery.

How to use U2U CAML Query Builder tool for SharePoint:

Learn CAML Query using U2U CAML Query Builder Tool

There are tools available in the 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. The 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>
You may use CAML Query to create SharePoint List.

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

Hope you like this blog. Keep following this blog

 

You may like other blogs –

Interview Questions and Answers Series –

 

You may love to read some of the popular blogs on SharePointCafe.Net:
 

No related posts.

16 thoughts on “CAML Query Tips, Tricks and Syntax”

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

    Reply

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

    Reply

Leave a Comment

RSS
Facebook
Twitter
YouTube
YouTube
Instagram