A CAML Query Quick Reference

Web Design & Development

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.

Column/Field Type Value Type Example Notes
Single Line of Text Text
<Query>
  <Where>
    <Eq>
      <FieldRef Name="Title" />
      <Value Type="Text">Hello World!</Value>
    </Eq>
  </Where>
</Query>
This is one of the simplest queries. The example selects items with a title equal to “Hello World!”
Multiple Lines of Text Text
<Query>
  <Where>
    <Contains>
      <FieldRef Name="Body" />
      <Value Type="Text"><![CDATA[</a>]]></Value>
    </Contains>
  </Where>
</Query>
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) Text
<Query>
  <Where>
    <Eq>
      <FieldRef Name="Author" />
      <Value Type="Text">Josh McCarty</Value>
    </Eq>
  </Where>
</Query>
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) Integer
<Query>
  <Where>
    <Eq>
      <FieldRef Name="Author" LookupId="TRUE" />
      <Value Type="Integer"><UserID /></Value>
    </Eq>
  </Where>
</Query>
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) Lookup
<Query>
  <Where>
    <Eq>
      <FieldRef Name="State" />
      <Value Type="Lookup">Arizona</Value>
    </Eq>
  </Where>
</Query>
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) Lookup
<Query>
  <Where>
    <Eq>
      <FieldRef Name="State" LookupId="TRUE" />
      <Value Type="Lookup">4</Value>
    </Eq>
  </Where>
</Query>
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) Date
<Query>
  <Where>
    <Eq>
      <FieldRef Name="Created" />
      <Value Type="DateTime">2012-01-10</Value>
    </Eq>
  </Where>
</Query>
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 />.
ContentTypeId ContentTypeId
<Query>
  <Where>
    <BeginsWith>
      <FieldRef Name="ContentTypeId" />
      <Value Type="ContentTypeId">"0x010100D5C2F139516B419D801AC6C18942554D</Value>
    </BeginsWith>
  </Where>
</Query>
This is useful for querying items with a specific content type or, if using <BeginsWith>, items with a specific content type or child content types. You can get the content type ID in the web UI by viewing that content type from the site collection or list content types (it should be in the URL).

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!


Comments

  1. I wish I could read here info about how to delete items via CAML.
    1. Hi senglory, Check out my SPTools project on GitHub. You can use it to delete items from a list by supplying a CAML query: https://github.com/joshmcrty/SPTools
      1. Thank you; this was exactly what I needed!
  2. Hi, My query for datetime works only when i use like : date.ToString("yyyy-MM-ddTHH\:mm\:ssZ")
  3. How do you update a "People or Group" column?
    1. figured it out... (ItemID) (userid);#(username);#(userid);#(username)
  4. 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
  5. 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]
    1. 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.
      1. 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.
  6. 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 ?
  7. Thank you! Save me a lot of googling around.

Comments are closed