Caml Queries to Remember

Article
06/09/2014

Everytime I start writing CAML I fall back to U2U.

Wanted to keep few queries handy so here we go

No Filter

<View><ViewFields><FieldRef Name="Title" /><FieldRef Name="Column2" /></ViewFields></View>

With Row Limit

<View><RowLimit>10</RowLimit><ViewFields><FieldRef Name="Title" /><FieldRef Name="Column2" /></ViewFields></View>

Above two can be used with CamlQuery object as view xml.

example

CamlQuery camlquery = new CamlQuery();
camlquery.ViewXml = "<View><RowLimit>10</RowLimit><ViewFields><FieldRef Name='Title'/><FieldRef Name='FileRef'/><FieldRef Name='BasePath'/></ViewFields></View>";

   List sSCConfigList = clientContext.Web.Lists.GetByTitle("SSCConfig");
               clientContext.Load(sSCConfigList);
               clientContext.ExecuteQuery();

var listItems = sSCConfigList.GetItems(camlquery);

Single Select

Example
<Query><Where><Eq><FieldRef Name="Title" /><Value Type="Text">ABC VALUE</Value></Eq></Where></Query>

Multiple Rows

Example
<Query><Where><Contains><FieldRef Name="Title" /><Value Type="Text">Some Text</Value></Contains></Where></Query>

In case of Rich Text field, you can use <![CDATA[]]> around the value to prevent parsing errors when passing HTML into the query. Or you can replace < with < , > with > and “ with " and so on.

Other operators are <Geq/> for greater than and <Leq/> for less than

LookUps

Here we use LookupId=”true”. By using this we can specify the id value in the value tag

Look up on Id

example

<Query><Where><Eq><FieldRef Name="SomeLookupcolumn" LookupId="TRUE" /><Value Type="Lookup">4</Value></Eq></Where></Query>

Here It will filter on ‘SomeLookupColumn’ column here based on look up id and not value. This ensures unique value.

This can be used to get person or group also

example

Filter on Current User

Example
<Query><Where><Eq><FieldRef Name="Author" LookupId="TRUE" /><Value Type="Integer"><UserID /></Value></Eq></Where></Query>

Notice here Valuetype is integer.

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.

Lookup on text

Example
<Query><Where><Eq><FieldRef Name="SomeLookupColumn" /><Value Type="Lookup">GujaratState</Value></Eq></Where></Query>

This will look for items with “GujaratState” in the look up column field. If there are more than one items having same display name, it will return all those items.

Date & Time

Example
<Query><Where><Eq><FieldRef Name="Modified" /><Value Type="DateTime"><Today /></Value></Eq></Where></Query>

A date can also be used instead in Value tag. We can also use something like this <Today OffsetDays="-4" />

Comments

Anonymous
February 12, 2015
Greate post. Thx - this is very helpful
- Anonymous
  October 17, 2016
  good
Anonymous
October 17, 2016
hui

Share via