CAML Queries

CAML is a markup language like html that allows developers to do queries against SharePoint lists, it’s syntax is very easy to understand and it allows to add logical conditions like Where, Contains, And, Or, etc, just like a SQL Query.

For one of our projects we have the need to do a filter on SharePoint views, the problem here is that the view it’s a list containing a CAML Query with the filters the view may have, so in order to filter the view that’s already been filtered before, we need to append our filters to the existing CAML Query.

CAML can be used to do the following:

  • Provide schema definition to the Web site provisioning system about how the site looks and acts.
  • Define views and forms for data and page rendering or execution.
  • Act as a rendering language that performs functions in the DLL like pulling a value from a particular field.
  • Provide batch functionality for posting multiple commands to the server using protocol.

CAML can be used in various ways to customize a SharePoint site, including the following:

  • In script or code that implements members of the SharePoint Foundation object model, where CAML strings are passed through method parameters, assigned to properties, or returned by methods and properties
  • In SOAP messaging that passes CAML strings to a SharePoint Foundation Web service to interact remotely with a deployment
  • In front-end site definitions used to instantiate SharePoint sites
  • In SharePoint Foundation Features to add specific functionality within a particular scope

Query Syntax:

1.       Logical Joins:

a.        And: Elements can be nested inside other And elements. Eg. <And><expression1><expression2></And>

b.      OR: Elements can be nested inside other Or elements. Eg.

<OR><expression1><expression2></OR>

2. Comparison Operator

a.       BeginsWith:  Searches for a string at the start of a column that holds Text of the given value.

<BeginsWith><FieldRef Name = “ColumnName”/><Value Type = “Value”/><XML /></BeginsWith>

b.      Contains: Searches for a string anywhere within a column that holds Text of field type values.

<Contains><FieldRef Name = “ColumnName”/><Value Type = “Value”/><XML /></Contains>

c.       Neq: Arithmetic operator that means “not equal to” and is used in queries.

<Neq>  <FieldRef Name = “Field_Name”/>  <Value Type = “Field_Type”/>  <XML /> </Neq>

d.      Eq: Arithmetic operator that means “equal to” and is used within a query.

<Eq> <FieldRef Name = “Field_Name”/>  <Value Type = “Field_Type”/>  <XML /></Eq>

e.      In: Specifies whether the value of a list item for the field specified by the FieldRef element is equal to one of the values specified by the Values element.

<In><FieldRef Name = “Field_Name”/><Values><Value Type = “Field_Type”/></Values><XML /></In>

f.        Gt: Arithmetic operator that means “greater than.” This element is used similarly to the Eq elements.

<Gt><FieldRef Name = “Field_Name”/><Value Type = “Field_Type”/><XML /></Gt>

g.       IsNotNull: Used within a query to return items that are not empty (Null).

<IsNotNull><FieldRef Name = “Field_Name”/><Value Type = “Field_Type”/><XML /></IsNotNull>

h.      IsNull: Used within a query to return items that are empty (Null).

<IsNull><FieldRef Name = “Field_Name”/><Value Type = “Field_Type”/><XML /></IsNull>

i.         Includes: If the specified field is a Lookup field that allows multiple values, specifies that the Value element is included in the list item for the field that is specified by the FieldRef element.

<Includes><FieldRef    Name = “Field_Name”/><Value    Type = “Field_Type”/><XML /></Includes>

j.        Lt : Arithmetic operator that means “less than” and is used in queries in views. This element is used similarly to the Eq and Gt elements.

<Lt><FieldRef Name = “Field_Name”/><Value Type = “Field_Type”/><XML /></Lt>

k.       NotIncludes: If the specified field is a Lookup field that allows multiple values, specifies that the Value element is excluded from the list item for the field that is specified by the FieldRef element.

<NotIncludes><FieldRef    Name = “Field_Name”/><Value    Type = “Field_Type”/><XML /></NotIncludes>

 

3. Group Operators

a.       GroupBy: Contains a Group By section for grouping the data returned through a query in a list view.

<GroupBy  Collapse = “TRUE” | “FALSE”><FieldRef Name = “Field_Name”/></GroupBy>

b. OrderBy: Determines the sort order for a query. The OrderBy element contains a group of FieldRef elements.

<OrderBy  Override = “TRUE” | “FALSE”  UseIndexForOrderBy = “TRUE” | “FALSE”>

<FieldRef     Ascending = “TRUE” | “FALSE”    Name = “Text” />

</OrderBy>

4. Value Elements

a. Month: Used in the DateRangesOverlap element to retrieve from a calendar all instances of a recurring event that occur within a month.

<DateRangesOverlap>  <FieldRef Name= “EventDate” />  <FieldRef Name= “EndDate” />  <FieldRef Name= “RecurrenceID” />  <Value Type=\”DateTime\”>    <Month />  </Value></DateRangesOverlap>

b. Now: Returns the current date and time.

<Now></Now>

c. Today: Renders the current date in the format that is relative to the server’s local time zone. For servers in the United States, the format is MM/DD/YYYY (for example, 1/21/2001).

<Today  Offset = “Integer”></Today>

d. UserId: Contains the unique ID number of the currently authenticated user of a site, as defined in the UserInfo table of the content database.

<UserID></UserID>

e. XML: Serves as an outer wrapper to denote an XML data island. This element does not render anything by itself.

<XML></XML>

Simple Example:

<Where>

<And>

<Neq>

<FieldRef Name=’Status’ />

<Value Type=’Text’>Accepted</Value>

</Neq>

<Eq>

<FieldRef Name=”AssignedTo”/>

<Value Type=”Integer”><UserID/></Value>

</Eq>

</And>

</Where>

<OrderBy>

<FieldRef Name=’Created’ Ascending=’True’ />

</OrderBy>

*note:- I have tried to collate as much relevant information which I thought would be useful. The information has been sourced from different site on the internet and my own know how of CAML.

Advertisements

3 thoughts on “CAML Queries

  1. I was looking for the solution to apply the filter like get the records assigned to user who has logged in currently.After a lot of googling i found your blog and Viola!
    Thank you so much Yogeshji.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s