Friday, October 2, 2009

CAML & SPQuery in SharePoint


Using Collaborative Application Markup Language (CAML)  queries with SPQuery and SPSiteDataQuery is a faster and more efficient way of retrieving items based on known criteria compare with for each on the SPListItemsCollection and checking for the criteria.

In this tutorial I’m going to discuss how you can write CAML queries for retrieving items in SharePoint List.

Operators

These are some common operators you can use with CAML queries.

  1. Eq--Equals
  2. Neq--Not equal
  3. Gt--Greater than
  4. Geq--Greater than or equal
  5. Lt--Lower than
  6. Leq--Lower than
  7. IsNull--Is null
  8. BeginsWith--Begins with
  9. Contains--Contains

Get All Items

This is discussed in SharePoint List C# Part 1. There you can write the query as follows. There you can get all the items in the list.

myquery.Query = "";

Single Criteria

Using following query you can get all the tasks where “ID” is equal to 5.

<Where>
<Eq>
<FieldRef Name='ID' />
<Value Type='Counter'>5</Value>
</Eq>
</Where>

You can get Value Types for different common type of columns using following list

e.g. Use Value Type "Text" for column type "Single line of text".

  1. Single line of text--Text
  2. Multiple lines of text--Note
  3. Choice (menu to choose from)--Choice
  4. Number (1, 1.0, 100)--Number
  5. Date and Time--DateTime
  6. Lookup (information already on this site)--Lookup/LookupMulti
  7. Yes/No (check box)--Boolean
  8. Person or Group--User

Using AND

This will give you the items where ID = 5 and Title =  "ABC".

<Where><And>
<Eq>
<FieldRef Name='ID' />
<Value Type='Counter'>5</Value>
</Eq>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>ABC</Value>
</Eq>
</And></Where>

OR

This will give you the items where ID = 5 or Title =  "ABC"

<Where><OR>
<Eq>
<FieldRef Name='ID' />
<Value Type='Counter'>5</Value>
</Eq>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>ABC</Value>
</Eq>
</OR></Where>

It is different if you want to use more than one "AND" or "OR" operator. As a example if you want to get items where ID = 5 and Title = "ABC" and created by "John", then you can write this.

<Where>
<And>
<And>
<Eq>
<FieldRef Name='ID' />
<Value Type='Counter'>5</Value>
</Eq>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>ABC</Value>
</Eq>
</And>
<Eq>
<FieldRef Name='Author' />
<Value Type='User'>John</Value>
</Eq>
</And>
</Where>

To get the correct FieldRef Name write accurate queries you can use U2U CAML Query Builder.

Remember though you rename a column the FieldRef Name will hold same value.

10 comments:

  1. Hello Saranga,

    Thanks for posting these! They've been very helpful for me :).

    ReplyDelete
  2. Thank you very much..narration is beautiful !!

    ReplyDelete
  3. @ praveen,
    Thanks for your valuable comment.
    Saranga Rathnayake

    ReplyDelete
  4. Nice and straight forward article, i just want to inquire something about CAML query. Is it possible to return a new derived column in the query like we do in SQL query. For example if query is returning FirstName and LastName can i return a third column with the name FullName on the fly by concatenating the two columns.

    Thanks in advance
    Adil Hussain

    ReplyDelete
  5. @ Adil Hussain,
    Thanks for your comment, according to my knowledge this is not possible, sorry !
    thanks !

    ReplyDelete
  6. hi,
    i have problem if possible please solve this,

    I created one relationship list with 2 lookup colomns [i.e curriculumid,topicid]one usr enter into list select curriculum1 and topic1 that is stored into ILP_curriculum_topic_id table ,when new topic is entered that time same user select the new topic also,so how to store the new topic under ILP_curriculum_topic_id .
    please tell me

    thanks
    Anjireddy

    ReplyDelete
  7. thanks a lot Saranya. This helped :)

    ReplyDelete
  8. nice.. Once you have CAML query
    You can get list items from sharepoint using SPQuery
    Check it out:

    How to get list items using caml and SPQuery

    ReplyDelete