Monday, 8 June 2015

CAML Query 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.

CAML query helps to fetch items from SharePoint list, CAML query is use to update, delete record from SharePoint list and library.

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:

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>

12 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