Posted by: R Manimaran | March 11, 2011

New in SharePoint 2010 CAML Query Operators -IN,INCLUDES,NOTINCLUDES

New in SharePoint 2010 CAML Query <IN>,<INCLUDES> &< NOT INCLUDES>

Even though Microsoft encourages using LINQ in SharePoint 2010, they introduced some new CAML Query schema elements. They are

· IN

· INCLUDES

· NOT INCLUDES

IN:

This is something work as same as what we see in SQL Query. We can provide a collection of Items to filter. Previously in SharePoint 2007 we have to use OR operator to do this.. But using OR for multiple times for multiple values will lead to lack in code clarity. So IN operator is considering as good.

Here I have following SharePoint List.

In the above list if want to select the items having designation like Engineer and Architect I have to write like this in SharePoint 2007.


<Where>

<Or>

<Eq>

<FieldRef Name='Designation' />

<Value Type='Text'>Engineer</Value>

</Eq>

<Eq>

<FieldRef Name='Designation' />

<Value Type='Text'>Architect</Value>

</Eq>

</Or>

</Where>

The above can be written in SharePoint 2010 as


<Where>

<In>

<FieldRef Name='Designation' />

<Values>

<Value Type='Text'>Engineer</Value>

<Value Type='Text'>Architect</Value>

</Values>

</In>

</Where>

Both will yield the same output as shown below.

INCLUDES:

MSDN Def: 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.

If you did not understand the above definition from MSDN, check the following example.

Here is a sample Employee list.

Here Specialization is a MultiSelect Lookup column.

I need to select People who are specialized in XML first and then specialized .Net also. This can be done using Includes.

SPQuery needs to framed like this


<Where>

<And>

<Eq>

<FieldRef Name=""Specialization""/>

<Value Type=""Lookup"">XML</Value>

</Eq>

<Includes>

<FieldRef Name='Specialization'/>

<Value Type='Lookup'>.Net</Value>

</Includes>

</And>

</Where>

Then the specified output will be

NOTINCLUDES:

It will do opposite to what Includes will do.

In the above list I like to have all the people who are specialized in SQL Server but not specialized in Crystal Report.

Here my SPQuery


<Where>

<And>

<Eq>

<FieldRef Name=""Specialization""/>

<Value Type=""Lookup"">SQL Server</Value>

</Eq>

<NotIncludes>

<FieldRef Name='Specialization'/>

<Value Type='Lookup'>Crystal Report</Value>

</NotIncludes>

</And>

</Where>

Note: As you see in <IN> operation you have <Values> as the child. Here we can specify multiple items. In the MSDN they specify it is apply to <Includes> and <NotIncludes>. But it is not working. I don’t know. Why?

About these ads

Responses

  1. Hi,

    thank you for this post.

    Caml In, Includes, NotIncludes are not well doucmented in msdn

  2. Nice article

  3. Yup good one

  4. Excellent Article , Your are saved my time Manimaran.

    Thanks you very much.

    Regards
    Murali Arumugam

  5. Please help, How can we select listitems between two dates using caml query ?


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

Categories

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: