CAML Query in SharePoint 2010

In this blog I will explain  - What is CAML Query? and How to use CAML Query in real time development in SharePoint 2010?
CAML stands for Collaborative Application Markup Language. CAML query generally used on SharePoint objects like List, Library, List Template, Content Type etc. The most important topic to discuss in SharePoint 2010 environment is CAML Query.

If you are a fresher and want to learn CAML Query then 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.

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, CAML query is use to update, delete record from SharePoint list and library.
Apart from that you may apply various where clause to apply filter in your CAML Query.

Lets start learning CAML Query step by step.


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

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:

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


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

CAML Query Syntax:

CAML Query syntax will be a new experience as compare 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>


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)


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

Post a Comment

Popular posts from this blog

Calling REST APi from server side code - C#

All about SharePoint 2010 Content Type Hub

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX