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.