SharePoint Sharpener

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:

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.