A CAML Query Quick Reference

Posted on by Josh McCarty

I’ve been doing a lot of work with SPServices and SharePoint lists lately, and I find myself using the same CAML queries over and over. Unfortunately I don’t always remember how to format some of the more common queries, so I decided to make a quick reference.

There are already some good tools and resources out there for building CAML queries. U2U’s CAML Query Builder is usually what I fall back to when I need a complex query with multiple conditions. Matt Bramer’s roboCAML is shaping up very nicely as well. However, in many cases these are overkill and I just need a basic “get all items where field X is equal to value Y” query. Heck, sometimes I just want to hard-code the query myself because it’s good practice. A quick reference that shows what value type to use and in what format for the various field types is all I really need so I don’t have to hunt through several bookmarked articles to find the answer.

I intend to update this on occasion, so if you have any suggestions please leave them in the comments.

A CAML Query Quick Reference

Single Line of Text

Value Type Text
Example <Query><Where><Eq><FieldRef Name="Title" /><Value Type="Text">Hello World!</Value></Eq></Where></Query>
Notes This is one of the simplest queries. The example selects items with a title equal to “Hello World!”

Multiple Lines of Text

Value Type Text
Example <Query><Where><Contains><FieldRef Name="Body" /><Value Type="Text"><![CDATA[</a>]]></Value></Contains></Where></Query>
Notes If this is a Rich Text field, you can use <![CDATA[]]> around the value to prevent parsing errors when passing HTML into the query. Alternatively, you can encode the HTML by replacing < with &lt;, > with &gt;, and " with &quot;. This query uses <Contains> to return any items that contain a hyperlink in the body field by looking for the closing </a> tag.

Person or Group (By Name)

Value Type Text
Example <Query><Where><Eq><FieldRef Name="Author" /><Value Type="Text">Josh McCarty</Value></Eq></Where></Query>
Notes This will look for items created by any user with “Josh McCarty” in the Name field of the User Information list. If more than one person has the same display name in the user list, it will select items created by all users with that name.

Person or Group (By ID)

Value Type Integer
Example <Query><Where><Eq><FieldRef Name="Author" LookupId="TRUE" /><Value Type="Integer"><UserID /></Value></Eq></Where></Query>
Notes By adding LookupId="TRUE" to the <FieldRef /> and using <UserID /> as the value, the query will filter based on the current user. You can also pass the ID of a specific user in place of <UserID /> (e.g. <Value Type="Integer">283</Value>) if you don’t want to filter by the current user. IDs are always unique, so this method ensures that only one user is a valid value.

Lookup (By Text)

Value Type Lookup
Example <Query><Where><Eq><FieldRef Name="State" /><Value Type="Lookup">Arizona</Value></Eq></Where></Query>
Notes This will look for items with “Arizona” in the State field. If more than one state has the same display name (not likely in this example, but for other lookups it could happen), it will return items from all states with that display name.

Lookup (By ID)

Value Type Lookup
Example <Query><Where><Eq><FieldRef Name="State" LookupId="TRUE" /><Value Type="Lookup">4</Value></Eq></Where></Query>
Notes By adding LookupId="TRUE" to the <FieldRef />, the query will filter based on the ID of the lookup rather than the text value. IDs are always unique, so this method ensures that only one state is a valid value.

Date (Day Only)

Value Type Date
Example <Query><Where><Eq><FieldRef Name="Created" /><Value Type="DateTime">2012-01-10</Value></Eq></Where></Query>
Notes This type of query seems to work whether the value type is set to DateTime or just Date as long as the value is formatted properly (yyyy-mm-dd). It also works if <Today /> is used as the value (you can offset the current date; e.g. use <Today OffsetDays="-7" /> for 7 days ago). See one of my previous posts regarding current date offsets for some more information about using <Today />.

I’ll be adding more column types to this post as I have time to write them, and I’ll probably create a PDF once they are all finished. For now I wanted to get this published sooner rather than later. If you have any suggestions or sample queries, leave them in the comments!

About Josh McCarty

Josh McCarty is a web designer/developer and all-around technology enthusiast located near Phoenix, AZ. He specializes in developing innovative SharePoint solutions that go beyond the out-of-the-box capabilities of the platform using jQuery, XSLT, and other tools in the “middle tier.” Josh also develops custom websites and plugins for the WordPress platform with a focus on HTML5/CSS3 and responsive design. In all of his projects he emphasizes the user-experience, promotes modern web standards, and leverages the latest technologies available.

13 Responses to A CAML Query Quick Reference

  1. senglory says:
    June 18, 2012 at 4:14 am

    I wish I could read here info about how to delete items via CAML.

    Reply
  2. VIKAS MEHTA says:
    July 26, 2012 at 2:02 pm

    Hi,
    My query for datetime works only when i use like :
    date.ToString(“yyyy-MM-ddTHH\\:mm\\:ssZ”)

    Reply
  3. Battsengel says:
    August 9, 2012 at 12:07 am

    Thanks a lot =)

    Reply
  4. Joe Engle says:
    August 22, 2012 at 7:01 am

    How do you update a “People or Group” column?

    Reply

  5. Pingback: CAML Examples with SPQuery | Bobs Blog

  6. westerdaled says:
    March 19, 2013 at 8:18 am

    Excellent esp the way you have layed the infomation out.
    ” + ssUser + ” .

    I am not user integer for my field definition as you have. Can I still the filter the way I have. – getting some odd errors in my javascript/csom

    Reply
  7. westerdaled says:
    March 19, 2013 at 8:21 am

    I have tried to delimit my CAML so that is appears in the comment
    [View][Query][Where][Eq][FieldRef Name='SSUser' /][Value Type='User']” + ssUser + “[/Value][/Eq][/Query][/View]

    Reply
    • Josh McCarty says:
      March 20, 2013 at 12:37 pm

      Is ssUser the name of the user or the ID? If it’s the ID, you’ll need to add LookupId='TRUE' to the <FieldRef /> element in your CAML.

      Reply
      • Daniel says:
        March 20, 2013 at 1:17 pm

        Good question as this always confuses me. In my list defs CAML it is Type=User. Which means I can use the people picker. However this may be stored as a unique ID for the Site Collection. Hence, I am not sure.

        Reply
  8. Willy says:
    April 19, 2013 at 2:56 am

    Hello,

    How can i select all items in a subfolder and only in this subfolder.
    for example
    2012
    January
    Marsh
    April

    And juste get Items in 2012/April ?

    Reply

Leave a Reply