SharePoint CAML queries samples

CAML query is the way to get data from SharePoint lists. However, writing and debugging CAML queries is a headache, so I’ll post some samples of queries here. First to query SharePoint data programmatically you need to use SPQuery object like this

SPList myList = SPContext.Current.Web.Lists["My List"];
SPQuery query = new SPQuery();
query.ViewAttributes = "Scope=\"Recursive\""; // get data from all folders
query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>MyItem</Value></Eq></Where>";
SPListItemCollection items = myList.GetItems(query);
1. Quering lookup field by lookupId

<Where>
    <Eq>
        <FieldRef Name='MyLookupField' LookupId='TRUE'/>
        <Value Type='Lookup'>22</Value>
    </Eq>
</Where>
2. Quering user field by user id
It’s quite the same as quering lookup field, because user field type is inherited from lookup type

<Where>
    <Eq>
        <FieldRef Name='CreatedBy' LookupId='TRUE'/>
        <Value Type='Lookup'>1</Value>
    </Eq>
</Where>
3. Quering user field by current user
Use <UserID> to get current user’s id

<Where>
    <Eq>
        <FieldRef Name='CreatedBy' LookupId='TRUE'/>
        <Value Type='Lookup'><UserID/></Value>
    </Eq>
</Where>
4. Quering datetime field
The trick here is that date should be in ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ). You can use SPUtility.CreateISO8601DateTimeFromSystemDateTime function to convert date to proper string format.

<Where>
    <Geq>
        <FieldRef Name='Created'/>
        <Value Type='DateTime' IncludeTimeValue='True'>2011-05-01T00:10:00Z</Value>
    </Geq>
</Where>
5. Quering integer field
You can use this query to search items by ID (since ID field type is Interger)

<Where>
    <Geq>
        <FieldRef Name='ID'/>
        <Value Type='Integer'>10</Value>
    </Geq>
</Where>
6. Quering boolean field
The thing to remember is that boolean is integer field indeed, so query it as integer: 0 – false, 1 – true.

<Where>
    <Eq>
        <FieldRef Name='MyBoolField'/>
        <Value Type='Integer'>1</Value>
    </Eq>
</Where>
7. Composite queries
Problem with composite queries is that and tags can contains only 2 child nodes, so you need to constract you query attentionally, e.g. if you want to make query A & B & C, it should look like following:>

<And>
    <Eq>
        <FieldRef Name='CreatedBy' LookupId='TRUE'/>
        <Value Type='Lookup'><UserID/></Value>
    </Eq>
    <And>
        <Geq>
            <FieldRef Name='Created'/>
            <Value Type='DateTime'>2011-05-01T00:10:00Z</Value>
        </Geq>
        <Eq>
            <FieldRef Name='MyLookupField' LookupId='TRUE'/>
            <Value Type='Lookup'>22</Value>
        </Eq>
    </And>
</And>

Reference:

http://techno-sharepoint.blogspot.in/2012/10/sharepoint-caml-queries-samples.html