SharePoint Sharpener

Obsessively Sharpening SharePoint

Several ANDs in a CAML Query

with 8 comments


If you are used to writing SQL queries to extract info from databases, using CAML to show data from SharePoint lists can be a bit of a challenge.

The syntax can be slightly cumbersome especially if you’re writing complex queries. Nesting more than a couple of conditions is confusing at first but once you get the hang of it, you’re cruising…

 

Simple AND example

The below example is the SQL equivalent to SELECT * FROM [Table] WHERE Title = ‘foo’:

<Query>
   <Where>
      <Eq>
         <FieldRef Name=’Title’ />
         <Value Type=’Text’>foo</Value>
      </Eq>
   </Where>
</Query>

Now, what happens if you need to do the equivalent of SELECT * FROM [Table] WHERE Title = ‘foo’ AND Name = ‘bar’? Look at this:

<Query>
   <Where>
      <And>
         <Eq>
            <FieldRef Name=’Title’ />
            <Value Type=’Text’>foo</Value>
         </Eq>
         <Eq>
            <FieldRef Name=’Name’ />
            <Value Type=’Text’>bar</Value>
         </Eq>
      </And>
   </Where>
</Query>

So far, so good. The 2 Eq conditions need to be encapsulated by an And.

 

Several ANDs in one query

What if we need three Ands or more in our query? I.e. SELECT * FROM [Table] WHERE Title = ‘foo’ AND Name = ‘bar’ AND Address = ‘foobar’. Check this out:

<Query>
   <Where>
      <And>
         <And>
            <Eq>
               <FieldRef Name=’Title’ />
               <Value Type=’Text’>foo</Value>
            </Eq>
            <Eq>
               <FieldRef Name=’Name’ />
               <Value Type=’Text’>bar</Value>
            </Eq>
         </And>
         <Eq>
            <FieldRef Name=’Address’ />
            <Value Type=’Text’>foobar</Value>
         </Eq>
      </And>
   </Where>
</Query>

The key is to know that the conditions are nested and not just listed in succession.

I’m sure you can guess how a query with 3 ANDs is put together. If not, you should download the excellent CAML Query Builder from U2U:

image

The CAML Query Builder lets you connect to your lists (locally or remotely) and build CAML queries using a point-and-click interface. Highly recommended.

Advertisements

Written by Thomas Sondergaard

June 27, 2008 at 11:57 am

Posted in Development

Tagged with

8 Responses

Subscribe to comments with RSS.

  1. Thanks a lot for the information. Earlier i was strugling while writing caml queries.
    But u have shown me a great tool. Is this tool for limited period of time?

    vivek

    December 18, 2008 at 4:11 pm

  2. No problem, Vivek, glad to help 🙂

    I don’t think U2U will start charging for the CAML Query Builder anytime soon. However, if they create a more fully featured and polished product, who knows…

    thomassondergaard

    December 18, 2008 at 10:57 pm

  3. Gr8 post
    But I want to create dynamic CAML query

    How do i do it..?

    sam

    December 31, 2008 at 12:38 pm

  4. Well, I guess you’ll have to create some sort of recursive method in your code to build the CAML query dynamically. Is that what you had in mind?

    thomassondergaard

    January 4, 2009 at 12:12 pm

  5. So Microsoft strayed from the standard that the entire known universe to create this monstrosity, and now I can download a program to help me create AND clauses.

    How much does ubuntu apache mysql and RoR cost again?

    asdlfj

    January 8, 2009 at 11:24 pm

  6. It’s too good thanks a lot……..

    Rajesh

    September 6, 2010 at 3:20 pm

  7. This site was… how do I say it? Relevant!! Finally I have found something
    that helped me. Thanks a lot!

    Scott Tucker

    December 30, 2012 at 10:46 am


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

%d bloggers like this: