CAML Queries For SharePoint Fields

Collaborative Application Markup Language (CAML) is an XML based markup language to define queries against list data. You can use CAML query to get the filtered, grouped or sorted data from SharePoint list.  CAML query can be used to retrieve SharePoint data in SharePoint object model, Web Service as well as PowerShell.
Following is the basic structure of CAML query:

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" />
	<Value Type="DataType">Value</Value>
	</Eq>
	</Where>
	<GroupBy>
	<FieldRef Name="FieldName" />
	<FieldRef Name="FieldName" />
	</GroupBy>
	<OrderBy>
	<FieldRef Name="FieldName" Ascending="TRUE"></FieldRef>
	<FieldRef Name="FieldName" Ascending="FALSE"></FieldRef>
	</OrderBy>
	</Query>

view raw CAML_Structure.xml hosted with ❤ by GitHub

Followings are the operators to use under Where element:

Logical Operators

And Or are the logical operators you can use to filter the data on multiple fields.

Comparison Operators

Operator	Meaning
Eq	= (Equal To)
Neq	<> (Not Equal To)
Lt	< (Less Than)
Gt	> (Greater Than)
Geq	>= (Greater Than Or Equal To)
Leq	<= (Less Than Or Equal To)
Contains	Like
IsNull	Null
IsNotNull	Not Null
BeginsWith	Beginning with the word
DateRangesOverlap	Compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap

Order/Group Operators

Operator	Meaning
OrderBy	Specify the sort order. Query can be sort by multiple fields.
GroupBy	Specify the grouping for data. Data can be group by multiple fields

Followings are few snippets of CAML query to filter on the different types of fields in SharePoint. 

Text, Choice, Number, Currency, Boolean

Replace the DataType in the query. Followings are the datatypes:

DataType	Field
Text	Single line of text
Choice	Choice field (menu to choose from)
Number	Number field (1, 1.0, 100)
Currency	Currency field ($, ¥, €)
Boolean	Yes/No field (check box) 1 = Yes 0 = No

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" />
	<Value Type="DATA_TYPE">Value</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_Fields.xml hosted with ❤ by GitHub

	SPQuery query = new SPQuery
	{
	Query = string.Format(@"<Where>
	<Eq>
	<FieldRef Name='{0}' />
	<Value Type='DATA_TYPE'>{1}</Value>
	</Eq>
	</Where>", "FieldName", "Value"),
	RowLimit = 10,
	ViewFields = string.Concat("<FieldRef Name='FieldName' />",
	"<FieldRef Name='FieldName' />")
	};

view raw SPQuery_Fields.cs hosted with ❤ by GitHub

Date and Time

DateTime datatype can be used with multiple options. It can work with provided Date/DateTime or time part Today

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" />
	<Value Type="DateTime" IncludeTimeValue='FALSE'>2001-01-01</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_DateTime.xml hosted with ❤ by GitHub

	SPQuery query = new SPQuery
	{
	Query = string.Format(@"<Where>
	<Eq>
	<FieldRef Name='{0}' />
	<Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value>
	</Eq>
	</Where>", "FieldName", SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now)),
	RowLimit = 10,
	ViewFields = string.Concat("<FieldRef Name='FieldName' />",
	"<FieldRef Name='FieldName' />")
	};

view raw SPQuery_DateTime.cs hosted with ❤ by GitHub

Using Today

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" />
	<Value Type="DateTime" IncludeTimeValue="FALSE">
	<Today OffsetDays="-7" />
	</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_DateTime2.xml hosted with ❤ by GitHub

Lookup

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" LookupId="TRUE" />
	<Value Type="Lookup">1</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_Lookup.xml hosted with ❤ by GitHub

	SPQuery query = new SPQuery
	{
	Query = string.Format(@"<Where>
	<Eq>
	<FieldRef Name='{0}' LookupId='TRUE' />
	<Value Type='Lookup'>{1}</Value>
	</Eq>
	</Where>", "FieldName", "Value"),
	RowLimit = 1,
	ViewFields = string.Concat("<FieldRef Name='FieldName' />",
	"<FieldRef Name='FieldName' />")
	};

view raw SPQuery_Lookup.cs hosted with ❤ by GitHub

Person

By ID

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" LookupId="TRUE" />
	<Value Type="Integer">1</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_PersonID.xml hosted with ❤ by GitHub

	SPQuery query = new SPQuery
	{
	Query = string.Format(@"<Where>
	<Eq>
	<FieldRef Name='{0}' LookupId='TRUE' />
	<Value Type='Integer'>{1}</Value>
	</Eq>
	</Where>", "FieldName", "1"),
	RowLimit = 10,
	ViewFields = string.Concat("<FieldRef Name='FieldName' />",
	"<FieldRef Name='FieldName' />")
	};

view raw SPQuery_PersonID.cs hosted with ❤ by GitHub

By Name

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName" />
	<Value Type="User">Doe, John</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_PersonName.xml hosted with ❤ by GitHub

	SPQuery query = new SPQuery
	{
	Query = string.Format(@"<Where>
	<Eq>
	<FieldRef Name='{0}' />
	<Value Type='User'>{1}</Value>
	</Eq>
	</Where>", "FieldName", "Doe, John"),
	RowLimit = 10,
	ViewFields = string.Concat("<FieldRef Name='FieldName' />",
	"<FieldRef Name='FieldName' />")
	};

view raw SPQuery_PersonName.cs hosted with ❤ by GitHub

Taxonomy

	<Query>
	<Where>
	<Eq>
	<FieldRef Name="FieldName"/>
	<Value Type="Text">Value</Value>
	</Eq>
	</Where>
	</Query>

view raw CAML_Taxonomy.xml hosted with ❤ by GitHub

	SPQuery query = new SPQuery
	{
	Query = string.Format(@"<Where>
	<Eq>
	<FieldRef Name='{0}'/>
	<Value Type='Text'>{1}</Value>
	</Eq>
	</Where>", "FieldName", "Value"),
	RowLimit = 10,
	ViewFields = string.Concat("<FieldRef Name='FieldName' />",
	"<FieldRef Name='FieldName' />")
	};

view raw SPQuery_Taxonomy.cs hosted with ❤ by GitHub