May 8, 2012

CAML Query tutorial for SharePoint

In this article, we will understand the basics of CAML query in SharePoint.
What is CAML?
  Ø  CAML - Collaborative Application Markup Language
  Ø  XML- Extensible Markup Language based query language
  Ø  Used to perform a query operation against SharePoint Lists
How SharePoint List Items are retrieved?
SharePoint List data can be retrieved in any one of the following ways:
1. Using the SharePoint object model – used when code runs on the server (Example: Developing a web part or an application page)
2. Using the SharePoint Lists web service – used when your code doesn’t run on the server where the SharePoint is installed (Example: Developing a windows application)
3. Using Power shell –used mostly by the ADMIN of the SharePoint when they quickly want to retrieve some information from the SharePoint site
How does CAML query looks like?
As I already mentioned, it is XML based query language and it contains tags in it. The root element of the CAML query root element is Query. But it is not necessary to use Query element in the query you form.
Within the Query element you have two elements possible:
1. Where   – to filter the data
2. OrderBy – to categorize the data
A simple structure of the CAML query is as follows:
<Query>
          <Where>
                   <Eq>
                             <FieldRef Name=”FieldName” />
                             <Value Type=”DataType”>Value</Value>
                   </Eq>
          </Where>
          <OrderBy>
                             <FieldRef Name=”FieldName” />
                             <FieldRef Name=”FieldName” />
          </OrderBy>
</Query>
Operators in CAML Query
From the above structure, we came to know that it uses Where and OrderBy elements to retrieve the data from the list.
Let us know about the operators present in the CAML query and its usage:
Inside the Where element
1. Logical Operators - AND, OR
2. Comparison Operators - Listed Below in the table
AND – Which takes two conditions are satisfied
OR – Which takes when either of the conditions is satisfied
Comparison Operators

Inside the OrderBy/GroupBy element
OrderBy – Which orders or sort the data depends upon the field (FieldRef element) given.
GroupBy – Which groups the data depends upon the group by field (FieldRef element) given.
Examples
Logical & Comparison Operators
Use of AND, Gt, Leq
<Query>
<Where>
<And>
<Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
</Gt>
<Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
</Leq>
</And>
</Where>
</Query>
Use of OR, Gt, Leq
<Query>
<Where>
<Or>
    <Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
    </Gt>
                     <Leq>
  <FieldRef Name="Price" />
<Value Type="Number">2000</Value>
    </Leq>
               </Or>
       </Where>
</Query>
Use of BeginsWith, Leq
<Query>
<Where>
<And>
     <BeginsWith>
 <FieldRef Name="Title" />
 <Value Type="Text">M</Value>
     </BeginsWith>
     <Leq>
    <FieldRef Name="Quantity" />
<Value Type="Number">1000</Value>
     </Leq>
</And>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="False" />
</OrderBy>
</Query>
OrderBy Operator
<Query>
<Where>
<Or>
   <Gt>
<FieldRef Name="Quantity" />
<Value Type="Number">0</Value>
  </Gt>
    <Leq>
<FieldRef Name="Price" />
<Value Type="Number">2000</Value>
  </Leq>
</Or>
</Where>
<OrderBy>
<FieldRef Name="Price" Ascending="True" />
</OrderBy>
</Query>
Is it possible to write this queries without any errors manually?
Yes. But we will know the errors only after executing the program. Hence there is a free CAML query builder which will generate the query easily.
To know about where to download and how to use the CAML Query builder read this article.
To know how to use the CAML query in the Microsoft Visual Studio read this article.

15 comments:

  1. So.. how do you actually execute the query?

    ReplyDelete
    Replies
    1. You can use CAML query builder to execute the query which is explained in this post
      http://sharepoint-works.blogspot.com/2012/05/using-caml-query-builder-for-sharepoint.html
      Or
      You can use it in MS Visual Studio to execute the query
      http://sharepoint-works.blogspot.com/2012/05/caml-query-to-get-data-from-sharepoint.html

      Delete
  2. Hi Maruthachalam, nice article for beginners. You can also use the U2U CAML Builder see the link for more details http://tad.co.in/?p=468.

    ReplyDelete
  3. Thanks for taking the time. is appreciated

    ReplyDelete
  4. So with all the other query options that you've listed, why would you learn and use CAML? Indeed why did they create CAML in the first instance? It's ugly, it's like programming using XML (not much different to XSLT). The question is why? I would appreciate any insight on this.

    ReplyDelete
    Replies
    1. Hi zhuo,
      Read this article to know why CAML query is used in SharePoint for querying the data from the Lists and Libraries.
      Ref: http://msdn.microsoft.com/en-in/library/ee536691(v=office.14).aspx
      Hope this answers your question.
      To know how the data is queried from SharePoint read this article
      Ref: http://sharepoint-works.blogspot.in/2012/05/caml-query-to-get-data-from-sharepoint.html

      Delete
  5. very easy...is that..haha..enjoy it...
    Regards TanujKhurana

    ReplyDelete
  6. Hi..Nice article.
    Do you know if it is possible to use CAML to query for items based on the tags?(http://office.microsoft.com/en-in/sharepoint-server-help/use-tags-and-notes-to-share-information-with-colleagues-HA101810816.aspx)

    I did not find any way to obtain only items that have a specific tag.

    ReplyDelete
  7. Good article for beginners! thanks! Krishnamurthy :)

    ReplyDelete
  8. Hi Guys, Thanks to all for this good article

    One thing need to include here which will be helpful for all.

    Let us assume the name of custom list column is "State Code List" (with space in between)

    How do we keep in . Please explain with solution

    ReplyDelete
  9. do we put this query caml code into aspx ? please assist..

    ReplyDelete
    Replies
    1. Yes. Read this article to know more
      http://sharepoint-works.blogspot.com/2012/05/caml-query-to-get-data-from-sharepoint.html

      Delete
  10. Thank you for taking the time to write this. Just two points.

    The popups are making my brain want to listen to Justin Beiber recite Trump speeches.

    Using Right Quotation Mark unicode is troublesome. Please consider using '

    ReplyDelete

Dear Readers,

I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.