CAML Designer for SharePoint 2010

CAML (Collaborative Application Markup Language) is an XML-based query language that helps you querying and customizing SharePoint sites. The XML elements define various aspects of a SharePoint site.

The CAML designer is a tool that helps you build your CAML queries to query SharePoint lists. You can download it from the downloads tab of the BIWUG site. The version downloadable from the site is a click-onnce application, meaning that each time you start the application, it checks online if no newer version exists. If this is inconvenient to you (because f.e. your server is not connected to the internet) and you want to download an off-line version, you can click this link.

This version of the CAML designer contains the following functionality:

• Log on using the SharePoint server object model, or the SharePoint client object model, or the web services
• CAML snippets for querying a SharePoint list
• Code snippets for the SharePoint server object model, the .NET Client object, the SharePoint web services

Currently this version of the CAML designer only supports SharePoint 2010. Do you think we will need to support SharePoint 2007?

Login in

When you start the CAML Designer application you get the following screen:

You can click the Connect button or the CAML Query tab. It will both open the logon form:

In the first section you can fill out your URL. If you already connected to a SharePoint site using the CAML Designer, these URLs will be available in the dropdown.

As you can see, you can connect three ways:

• using the server object model: this is when you are able to install the tool on the server where you installed SharePoint.
• using the client object model: this is when you need to connect remotely.
• using the SharePoint web services: this is when you need to connect remotely. Didn’t test it but it is possible you can use it on a SharePoint 2007 site.

Currently you can only build CAML queries for SharePoint 2010.

If you need to logon the using the web services, you can use your own credentials, but you can also specify different credentials. In that case you can click the Custom credentials tab and fill out your details:

Click OK to logon to the SharePoint site and to start building CAML queries.

Getting started

Once you are logged on, a ribbon becomes available. The buttons on the ribbon will be explained in next section. This section gets you starting to get the feeling of the user interface.

In the left panel you can find a treeview with all the lists available in the site you are connected to. Selecting one of the lists will populate a panel with all fields of the list,  except the hidden fields.

To start building your CAML query, you can toggle between the buttons in the CAML Query group. More on these buttons here under.

Support SharePoint Online

The CAML Designer can also connect to Office365/SharePoint Online. You can fill out the URL to your SharePoint Online site. You don’t have to specify your credentials in the credentials box at the bottom of the logon dialog because they will be asked later on while authenticating. Don’t forget to switch to Client Object model or even the SharePoint Web Services; obiously the server object model can not be used to connect to SharePoint Online.

The buttons

Now your ready to start building CAML queries.  This is the ribbon:

General options

In the general options section you find following buttons:

• Execute: This button is checked by default. It executes the query that you are building dynamically so that you always see the results on the fly. If you encounter performance issues you can toggle this button off to stop the execution of the query.
• Clear: Use this button to clear the current CAML query. You can start over again.

CAML Query

These buttons represent the different clauses of the CAML query. Toggling one of these buttons will make an additional list box available. Once you select a field in the left panel, it will be added to the right panel with a user interface specific to the query clause. For example, if you toggle the OrderBy button to define a sort order, you will be able to define if the results should be sorted in ascending or descending order. Below you will find more detailed explanation of all the configuration possibilities.

• ViewFields: toggle this button if you want to define the columns that you want to be returned in your result set (it corresponds to the SELECT clause of a SQL query).
• Where: toggle this button if you want to define one or more filters.
• OrderBy: toggle this button if you want your result set to be sorted.
• Query Options: toggle this button if you want to set additional query options.

Each clause will be documented in detail in the following sections, and how you can use the CAML Designer to build up each of these clauses.

CAML Code

The CAML button is toggled on by default. It instructs the CAML Designer to show the pure CAML query in the left bottom panel of the user interface. The query is constructed on the fly while you are selecting fields and operators. More on this later on.

Code snippets

This group contains a number of buttons that is responsible for showing a code snippet based on the CAML query that is constructed in the user interface. Following code snippets are provided:

• The Server Object Model
• The .NET Client Object Model
• The web services

No other types of code snippets are available yet.

C#

Code snippets will only be provided in C#

The Home button

If you click on the blue button in the upper left corner of the ribbon, another menu drops and gives the user some extra options:

• Connect: will show the login screen so that the user can enter his/her credentials and start using the CamlDesigner
• Disconnect: will break the connection to the SharePoint server and will empty all the information like url and credentials
• Save Query:  opens up a save as dialogue window where the user can specify where to save the information that is in the code textbox. So if a user has selected “caml”, than that caml query will be saved in a caml file (text file). If the user selects “client om for .net” than the code will be saved in a caml file and not the actual caml query.

Once you have selected a list and all the fields of the list are available in the left panel, you can start building your query.

The OrderBy clause

If you need a sorted result set, you have to define a sort order. Click the field in the left panel on which you want to sort. The field will appear in the right panel with an image indicating the sort order. Just click the image if you want to change the sort order.

While you are building the sort order by clicking around, you will see your CAML query evoluate at the bottom of the screen. When in first instance the Title field is added, the CAML looks like the following:

<OrderBy>
  <FieldRef Name='Title' />
</OrderBy>

If no sort order is specified, the result set will be ordered in ascending order.

Select a second field from the left panel if you want to sort on more than one field. Your CAML query will immediately change as follows:

<OrderBy>
  <FieldRef Name='Title' />
  <FieldRef Name='StartDate' /> 
</OrderBy>

If you want to sort in descending order, click on the image next to the field name in the right panel and your CAML will look like this:

<OrderBy>
  <FieldRef Name='Title' />
  <FieldRef Name='StartDate' Ascending='FALSE'/> 
</OrderBy>

If the Execute button is toggled on, you will also see the resultset change at the bottom of the scrseen.

Remarks:

• If you encounter performance problems, you can toggle this button off.

If you want to change the sort order, you will have to remove the fields from the right panel and add them again in the right order.

On the ribbon of the CAML Designer, you also have a button group Code Snippets. By default the CAML button is selected, but you can also choose to view code snippets in the left bottom panel. Click the Server OM button if you want to learn how to set the sort order and pass it to an SPQuery object:

SPList spList = spWeb.Lists.TryGetList("Tests");
if (spList != null)
{   
    SPQuery qry = new SPQuery();
    qry.Query= "<OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>";
    SPListItemCollection listItems = spList.GetItems(qry);
}

For the .NET Client object model, the code snippet looks as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
            <Query>
                <OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>
            </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

If you need to pass your CAML query throught the lists.asmx web service, you can use the following code snippet:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

Execute button

Developers who downloaded the first version of the CAML Designer will notice that there is an additional Execute button at the bottom of the screen, right between the CAML code and the results grid. This button gives you the possibility to view the results after you have changed the CAML manually in the text box.

Remarks:

• This button only execute changed CAML; it will not execute changes in a code snippet.
• Your changes in the CAML text box will not cause changes in the code snippets generated with the ribbon buttons. If you want your changes to occur in the snippets, you have to build the query using the user interface.

The ViewFields clause

When you execute a query, the result set contains by default all the fields from the default view, plus a number of system columns like ID, Created, and Modified.

If you need only a limited set of columns returned in your result set, you will have to define a ViewFields clause.

Your CAML query looks like the following:

<ViewFields>
    <FieldRef Name='Title' />
    <FieldRef Name='StartDate' />
    <FieldRef Name='Countries' />    
</ViewFields>

You can use the following code snippet for the Server object model:

SPList spList = spWeb.Lists.TryGetList("Tests");
if (spList != null)
{   
    SPQuery qry = new SPQuery();
 qry.ViewFields= "<FieldRef Name="Title" /><FieldRef Name="StartDate" /><FieldRef Name="Countries" />"; 
    qry.Query= "<OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>"; 
    SPListItemCollection listItems = spList.GetItems(qry);
}

For the .NET Client object model, the code snippet looks as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
 <ViewFields> 
 <FieldRef Name="Title" /><FieldRef Name="StartDate" /><FieldRef Name="Countries" /> 
 </ViewFields>
           <Query>
                <OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>
            </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

If you need to pass your CAML query throught the lists.asmx web service, you can use the following code snippet:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='StartDate' /><FieldRef Name='Countries' />"; 
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

The Where clause

To avoid the retrieval of all the list items in a list or document library, you can filter data by defining a Where clause. Constructing a Where clause in CAML can be rather complex, especially in cases where you need more than one filter.

Click the Where button on the ribbon and select from the left panel a field for which you want to define a filter. The field is added to the right panel. It consists of 3 parts: the field name, an operator button and a control in which you can specify a value.

CAML contains following operators:

• Eq: equal
• Geq: greater than or equal
• Gt: greatoer then
• Leq: lower than or equal
• Lt: lower than
• BeginsWith
• Contains
• IsNull
• IsNotNull
• Includes (new in SharePoint 2010)
• In (new in Sharepoint 2010)

Right-click the operator button if you want to change the operator.

As of the moment that you start typing a value in the text box, you will see your CAML change, and the designer starts executing your query in the background.

Remarks: If you encounter performance problems, you can toggle the execute button off.

A simple Where clause in CAML looks like this:

 <Where>
     <Eq>
        <FieldRef Name='Title' />
        <Value Type='Text'>Test 1</Value>
     </Eq>
 </Where>

Another remark: special characters

It is possible that your text contains special characters like “test & run”. If you enter the value in a Where field like this, the CAML Designer will return an exception, nor your SPQuery would execute.

You can work around this problem by using the <![CDATA[test&run]]> construct.

To be honest, it does not work in all cases. With a Note field, for example it would not work out.

Reference material: http://praveenbattula.blogspot.be/2010/02/sharepoint-querying-caml-query-usage.html

Choice fields

Depending on the data type, the control where you can enter the value to filter on, will be different. You get a text box for fields of type text, note, number, counter and computed. If you want to filter on a boolean field, you get a check box.

But if you want to filter on a choice field or a multi choice field, you get a list box with all values configured for your choice field. You can select one of the values.

Once you select a value, the CAML query is constructed in the left bottom panel and executed against the list, showing the results in the left right table. A filter criteria on a choice field looks as follows:

 <Where>
     <Eq>
        <FieldRef Name='Choice' />
        <Value Type='Choice'>Value C</Value>
     </Eq>
 </Where>

Lookup fields

If your field is a lookup field or a multi lookup field, you also get a list box with all values of the lookup list.

Once you select a value, the CAML query is constructed in the left bottom panel and executed against the list, showing the results in the left right table. A filter criteria on a lookup field looks as follows:

 <Where>
     <Eq>
        <FieldRef Name='Countries' />
        <Value Type='Lookup'>Netherlands</Value>
     </Eq>
 </Where>

It is possible you want to filter on the ID of a lookup value, because values can change over time. In that case you can check the Query by ID check box and your CAML query will be changed into the following:

 <Where>
     <Eq>
        <FieldRef Name='Countries' LookupId='True'/>
        <Value Type='Lookup'>1</Value>
     </Eq>
 </Where>

Cross-site lookup fields

Some lookup fields get there data from a parent site. In that case the field definition contains a WebId with the Guid from the SharePoint site where the lookup list is located. CAML queries based on cross-site lookup fields can also be handled by the CAML Designer. The CAML itself is the same as for a normal lookup field; only the values need to be gathered from a different web.

DateTime fields

Filtering on a DateTime field is a bit special in CAML, so the user interface of a DateTime field offers a number of additional configuration options.

You can choose to query on today’s date or on a specific date. If you choose for today’s date, the CAML looks as follows:

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'><Today /></Value>
     </Lt>
 </Where>

You can also add or subtract a number of days from today’s date. In that case you have to add the Offset attribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days. You can also add an offset to the date:

Your CAML then looks as follows:

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'><Today Offset='5' /></Value>
     </Lt>
 </Where>

If you choose to filter on a specific date, a calendar is displayed.

Pick a date from the calendar and watch the CAML query change:

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'>2012-05-17T12:00:00</Value>
     </Lt>
 </Where>

This only works on dates. This query will return all list items with a start date before May 17th 2012, but not those starting before  12 o’clock. If you want your query to take into account the time part, you have to use a special attribute IncludeTimeValue that you can set on the FieldRef element or on the Value element (I tested it out, it works both ways):

 <Where>
     <Lt>
        <FieldRef Name='StartDate' />
        <Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-17T12:00:00</Value>
     </Lt>
 </Where>

When you click the Include Time Value check box in the user interface, you will be able to enter a time value in the format xx:xx:xx:

You can view the code snippet for the Server object model:

SPList spList = spWeb.Lists.TryGetList("Tests"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.Query =    @"
      <Where>
         <Lt>
            <FieldRef Name='StartDate' /> 
            <Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value>
         </Lt>
      </Where>";
    SPListItemCollection listItems = spList.GetItems(qry); 
}

For the .NET Client object model, the code snippet looks as follows:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query> 
               <Where><Lt><FieldRef Name="StartDate" />
              <Value Type="DateTime" IncludeTimeValue="TRUE">2012-05-15T10:30:00Z</Value></Lt></Where>
             </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

If you need to pass your CAML query throught the lists.asmx web service, you can use the following code snippet:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
 queryNode.InnerXml = "<Where><Lt><FieldRef Name='StartDate' />"
   + "<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value></Lt></Where>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

Managed Metadata fields

You can also filter on managed metadata fields. If you select such a field from the list of available fields, you will get a text box and a more button.

You can directly type in the term you want to filter on, but when you’re logged on using the SharePoint server object model and you click the … button, a dialog with a treeview appears. This treeview is populated with the values of the term store and the term set to which the field is configured:

If your managed metadata field is a normal taxonomy field, the selected term will be displayed in the selected text box. This results in the following CAML:

 <Where>
     <Eq>
        <FieldRef Name='Domain' />
        <Value Type='TaxonomyFieldType'>Contracts</Value>
     </Eq>
 </Where>

The results grid looks like this in my case:

If your managed metadata field is a multi select field, all selected terms are listed in the text box, separated by a ; sign:

This results in the following CAML query:

 <Where>
     <Eq>
        <FieldRef Name='MultiDomain' />
        <Value Type='TaxonomyFieldTypeMulti'>Contracts;Computer Plan</Value>
     </Eq>
 </Where>

Of course, a where clause written like this does not always result in the expected results. In my case the results grid stayed empty. I could make it work by changing the operator from Eq to In.

When you open the dialog again, the selected terms will be displayed in the text box, but now separated with a plus sign:

This translates in the following CAML code:

 <Where>
     <In>
        <FieldRef Name='MultiDomain' />
        <Values>
           <Value Type='TaxonomyFieldTypeMulti'>Computer Plan</Value>
           <Value Type='TaxonomyFieldTypeMulti'>Contracts</Value>
        </Values>
     </In>
 </Where>

And now I have results showing up in my result grid.

If you’re connected using the Client Object Model, the support for managed metadata is rather limited. When you click the … button you will get a dialog that shows all terms that have already been used for the field:

When you’re connected using the SharePoint web services, you will only get a text box where you can enter a term.

Multiple filter criteria

If you want to specify two filter criteria you also have to specify a join operator And or Or. By selecting a second field from the available fields panel, this join operator is added automatically. The default value is And.

Click on the control to toggle it to Or.

In CAML it looks as follows:

<Where>
   <And>
      <BeginsWith>
        <FieldRef Name='Title'
        <Value Type='Text'>Test</Value>
     </BeginsWith>
     <Lt>
        <FieldRef Name='StartDate' />
        <Value Type='DateTime'><Today /></Value>
     </Lt>
   </And>
</Where>

You can add as many filter criteria as need.

In CAML the filter criteria are nested in a very specific way:

<Where>
      <Or>
          <And>
             <Eq>
                <FieldRef Name='MultiLookup' />
                <Value Type='LookupMulti'>Antwerp</Value>
             </Eq>
             <Eq>
                <FieldRef Name='MultiLookup' />
                <Value Type='LookupMulti'>Ghent</Value>
             </Eq>
          </And>
          <Eq>
             <FieldRef Name='MultiLookup' />
             <Value Type='LookupMulti'>Breda</Value>
          </Eq>
       </Or> 
</Where>

For each extra criterion you have to add an extra join operator at the outside of the query and add the criterion at the end:

 <Where>
    <Or>
        <Or>
           <And>
              <Eq>
                 <FieldRef Name='MultiLookup' />
                 <Value Type='LookupMulti'>Antwerp</Value>
              </Eq>
              <Eq>
                 <FieldRef Name='MultiLookup' />
                 <Value Type='LookupMulti'>Ghent</Value>
              </Eq>
           </And>
           <Eq>
              <FieldRef Name='MultiLookup' />
              <Value Type='LookupMulti'>Breda</Value>
           </Eq>
        </Or> 
        <Eq>
            <FieldRef Name='Country' />
            <Value Type='Lookup'>India</Value>
        </Eq>
    </Or>
</Where>

Remove a field

If you want to remove a field from the Where clause, click the x button in the right upper corner of the field panel.

The QueryOptions

Executing a query is not only about CAML. When working with the SPQuery object you can set different properties to influence the returned list items. When working with the SharePoint web services, these options are translated into CAML and are part of the QueryOptions element.

Include mandatory columns

When specifying a ViewFields clause, only values for these fields are returned, together with a few system columns like ID, Created and Modified. You can also indicate that you want to have the required fields returned too in the resultset. You can do this by setting the IncludeMandatoryColumns to true.

In the CAML panel you will see an additional <QueryOptions> node.

 

But in the server object model, this information must be passed by setting the IncludeMandatoryColumns property to true:

SPList spList = spWeb.Lists.TryGetList("Tests"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.Query =    @"
      <Where>
         <Lt>
            <FieldRef Name='StartDate' /> 
            <Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value>
         </Lt>
      </Where>";
    qry.ViewFields = "<FieldRef Name="Title" /><FieldRef Name="StartDate" /><FieldRef Name="Countries" />"; 
    qry.IncludeMandatoryColumns = true;
    SPListItemCollection listItems = spList.GetItems(qry); 
}

If you need to use the lists.asmx web service to execute your CAML query, you have to pass a QueryOptions node:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
 queryNode.InnerXml = "<Where><Lt><FieldRef Name='StartDate' />"
   + "<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value></Lt></Where>";

XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='StartDate' /><FieldRef Name='Countries' />"; 

XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<IncludeMandatoryColumns>True</IncludeMandatoryColumns>"; 

System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

Remark: this options doesn’t seem to work with the Client Object model.

Row Limit

Another query option is the row limit. It can be used to limit the number of rows returned in the result set.

When working with the server object model, you can set the RowLimit property of SPQuery:

 qry.RowLimit = 2;

When working with the web services, you have to pass the value as follows:

System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

Also with the Client Object Model you can specify a row limit:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query> 
               <Where><Lt><FieldRef Name="StartDate" />
              <Value Type="DateTime" IncludeTimeValue="TRUE">2012-05-15T10:30:00Z</Value></Lt></Where>
             </Query>
             <RowLimit>2</RowLimit>
       </View>"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

Dates in UTC

Setting this Boolean property specifies whether the query returns dates in Coordinated Universal Time (UTC) format. In the CAML query you will see this option in the QueryOptions node. 

For the server object the syntax is similar to that of the previously described property:

 qry.DatesInUtc = true;

When working with the Client Object Model, you don’t have to pass it in the ViewXml property, you just have to set a property of the CamlQuery object:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query> 
               <Where><Lt><FieldRef Name="StartDate" />
              <Value Type="DateTime" IncludeTimeValue="TRUE">2012-05-15T10:30:00Z</Value></Lt></Where>
             </Query>
             <RowLimit>2</RowLimit>
       </View>"; 
 camlQuery.DatesInUtc = true;

    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

With the web services you have to pass the information as follows:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
 queryNode.InnerXml = "<Where><Lt><FieldRef Name='StartDate' />"
   + "<Value Type='DateTime' IncludeTimeValue='TRUE'>2012-05-15T10:30:00Z</Value></Lt></Where>";

XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='StartDate' /><FieldRef Name='Countries' />"; 

XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<DatesInUtc>True</DatesInUtc>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

Expand user fields

When you omit this property or set it to false, user fields will return the login name of the user. Setting this Boolean property to true  (or include it in the QueryOptions node), user fields are returned as follows:

Karine Bosch,#DEMO\karine,#karine@DEMO.COM,#,#Karine Bosch

The returned value includes the login name, e-mail address, Session Initiation Protocol (SIP) address, and title, when present, which causes a user field to behave as a multilookup field.  The syntax is similar to that of the previously described property.

For the server object model, you have to set the corresponding property to true:

 qry.ExpandUserField = true;

And for the GetListItems method of the lists.asmx you have to pass it as an element of the QueryOptions node:

XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ExpandUserField>True</ExpandUserField>";

Remark: this options doesn’t seem to work with the Client Object model.

Include attachment URLs

There are a number of query options that have to do with attachments:

Selecting this option will return the URL of the attachments of a list item.

Additionally if you select the Include attachment version, you also get additional information about the version.

For the moment I can only make it work when you are connected using the SharePoint web services using the following code:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<OrderBy><FieldRef Name="Title" /><FieldRef Name="StartDate" Ascending="FALSE" /></OrderBy>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='StartDate' /><FieldRef Name='Attachments' />"; 
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");

queryOptionsNode.InnerXml = "<IncludeAttachmentsUrls>True</IncludeAttachmentsUrls>"
 + "<IncludeAttachmentVersion>True</IncludeAttachmentVersion>";  
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

Don’t forget to select the Attachment field in the ViewFields clause, otherwise you will see no additional information.

Remark: this options doesn’t seem to work with the Client Object model. You have to add additional code to retrieve the attachments. Read my blog post on the client object model for more details.

Files and folder options

There are also a number of query options for working with files and folders in a document library. A folder is a special list item on a list or document library. If you execute a standard CAML query you will end up with all fiels and folders from the root folder. In my case, this is the content of the root folder of my Shared Documents library:

If you want to query all folders and sub folders of a list or document library, you have to define extra query options. The user interface of the CAML Designer gives you a wide range of options that you can configure.

Clicking the first option “Query all files and folders in root folder” will result in an empty query as this is the standard behavior. If you only want to retrieve the folders of the root folder, you have to add a Where clause that indicates that you only want to query the folders:

 <Where>
     <Eq>
        <FieldRef Name='FSObjType' />
        <Value Type='Integer'>1</Value>
     </Eq>
 </Where>

In my case this results in the following result set:

This CAML query is generated for you by clicking the second option “Query all folders in root folder“. Selecting the option “Query all files in root folder” generates this CAML:

 <Where>
     <Eq>
        <FieldRef Name='FSObjType' />
        <Value Type='Integer'>0</Value>
     </Eq>
 </Where>

It gets more interesting if you want to query through the hierarchy of sub folders. To retrieve all files and folders through all sub folders, you have to use the query option ViewAttributes. The CAML panel will show the following CAML:

 <QueryOptions>
     <ViewAttributes Scope='RecursiveAll' />
 </QueryOptions>

But the use is a bit different based on the way you are accessing the SharePoint data. If in your code you are working with the SharePoint server object model, you have to set the ViewAttributes property of the SPQuery object as follows:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.ViewAttributes = "Scope='RecursiveAll'";
    SPListItemCollection listItems = spList.GetItems(qry); 
}

If the code you are writing is using the .NET client object model, your query looks like this:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Shared Documents");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View Scope='RecursiveAll'></View>"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

If you need to retrieve all files and folders all folders deep using the SharePoint web services, your code will look like this:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query"); 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");

XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<View Attributes Scope='RecursiveAll' />"; 

System.Xml.XmlNode items = listsWS.GetListItems("Shared Documents", null, queryNode, viewfieldsNode, 
    null, queryOptionsNode, null);

Previous code samples were to retrieve all files and folders through all folders of the document library. If you only want to retrieve the files through all folders of the document library, your CAML will look like this:

 <Where>
     <Eq>
        <FieldRef Name='FSObjType' />
        <Value Type='Integer'>0</Value>
     </Eq>
 </Where>
 <QueryOptions>
     <ViewAttributes Scope='RecursiveAll' />
 </QueryOptions>

This translates into the following code snippet for use with the server object model:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();

    qry.Query =
    @"
       <Where>
          <Eq>
             <FieldRef Name='FSObjType' />
             <Value Type='Integer' >0</Value>
          </Eq>
       </Where>"; 
   qry.ViewAttributes = "Scope='RecursiveAll'";
    SPListItemCollection listItems = spList.GetItems(qry); 
}

The code snippet for the .NET client object model is the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Shared Documents");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View Scope='RecursiveAll'>
             <Query>
                 <Where><Eq><FieldRef Name='FSObjType'><Value Type='Integer'>0</Value></Eq></Where>
             </Query> 
         </View>;" 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

And this is the code snippet to use when working with the lists.asmx SharePoint web service:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query"); 

queryNode.InnerXml = "<Where><Eq><FieldRef Name='FSObjeType' />"    
   + "<Value Type='Integer'>0</Value></Eq></Where>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");

XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<View Attributes Scope='RecursiveAll' />"; 

System.Xml.XmlNode items = listsWS.GetListItems("Shared Documents", null, queryNode, viewfieldsNode, 
    null, queryOptionsNode, null);

Previous code always start of from the root folder of a document library. But you can also start querying from a sub folder of a document library. The CAML Designer offers an option where you can fill out the folder on which you want to query:

When you check the Query sub folder check box, an additional part of the query options control is expanded. You can fill out the relative path of the sub folder, starting with the name of the document library:

But the CAML query will only be constructed and executed after you selected one of the check boxes below the check box. When you select the first option to query all files and folders in this folder, your CAML only contains the following:

 <QueryOptions>
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

This is translated into the following snippet for the server object model:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.Folder = spList.RootFolder.SubFolders["/Shared Documents/Folder 1"];
    SPListItemCollection listItems = spList.GetItems(qry); 
}

In the .NET client object model it looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Tests");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
 camlQuery.FolderServerRelativeUrl = "/Shared Documents/Folder 1";

    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

And the call to the GetListItems method on the lists.asmx web service looks like this:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
 XmlNode queryNode = doc.CreateElement("Query");
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<Folder>/Shared Documents/Folder 1</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

Previous query returns all files and folders in the specified sub folder, but it is possible that you only want to query the files in this sub folder. In that case the CAML query contains an additional query options element:

 <QueryOptions>
     <ViewAttributes Scope='FilesOnly' /> 
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

The code snippet for the server object model looks like this:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.ViewAttributes = "Scope='FilesOnly'"; 
    qry.Folder = spList.RootFolder.SubFolders["/Shared Documents/Folder 1"];
    SPListItemCollection listItems = spList.GetItems(qry); 
}

The generated code snippet for the .NET client object model looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Shared Documents");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
 camlQuery.ViewXml = @"<View Scope='FilesOnly'></View>";     camlQuery.FolderServerRelativeUrl = "/Shared Documents/Folder 1"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

The code snippet when working with the SharePoint web services is the following:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ViewAttributes Scope='FilesOnly' /><Folder>/Shared Documents/Folder 1</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

It is also possible that you only want to query the files in this folder and its sub folders. In that case your CAML query changs into:

 <QueryOptions>
     <ViewAttributes Scope='Recursive' /> 
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

The code snippet for the server object model looks like this:

SPList spList = spWeb.Lists.TryGetList("Shared Documents"); 
if (spList != null) 
{
    SPQuery qry = new SPQuery();
    qry.ViewAttributes = "Scope='Recursive'"; 
    qry.Folder = spList.RootFolder.SubFolders["/Shared Documents/Folder 1"];
    SPListItemCollection listItems = spList.GetItems(qry); 
}

The generated code snippet for the .NET client object model looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Shared Documents");
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
 camlQuery.ViewXml = @"<View Scope='Recursive'></View>";  
 camlQuery.FolderServerRelativeUrl = "/Shared Documents/Folder 1"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
}

The code snippet when working with the SharePoint web services is the following:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
queryOptionsNode.InnerXml = "<ViewAttributes Scope='Recursive' /><Folder>/Shared Documents/Folder 1</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

If you want to query all files and folders in this folder and its sub folders. In that case you have to set the scope to RecursiveAll:

 <QueryOptions>
     <ViewAttributes Scope='RecursiveAll' /> 
     <Folder>/Shared Documents/Folder 1</Folder>
 </QueryOptions>

The SPSiteDataQuery

The CAML Designer also supports the SPSiteDataQuery. This object only exists on the server-side object model, so this option is only available when logged on with the server-side object model.

We now have a separate group with 2 buttons: CAML query and Site Data Query:

The CAML Query button is checked by default, but you can toggle it to Site Data Query. Don’t forget to select a list first.

Once the Site Data Query button clicked, the QueryOptions panel will become visible. By default it will contain the ID of the server template of the list you selected and the query will execute querying the current site. In my case I selected a Task list:

The CAML looks like the following:

  <QueryOptions>
      <Lists ServerTemplate='107' />
      <Webs Scope='Site' />
   </QueryOptions>

The first query option is the Lists element which requires a ServerTemplate attribute. The value should be an integer indicating the list server template. You can find a list of the standard server template IDs here.

The second query option is the Webs element which defines the scope for the query. There are 3 different scopes:

• Site: when the scope is set to Site, SPSiteDataQuery will query the current site.
• SiteCollection: this will instruct SPSiteDataQuery to query the whole site collection.
• Recursive: setting the scope to Recursive will instruct SPSiteDataQuery to query the current site and its sub sites

The code snippet for the server-side object model looks as follows:

SPSiteDataQuery qry = new SPSiteDataQuery(); 
qry.ViewFields = "<FieldRef Name='Title' />";  
qry.Lists = "<Lists ServerTemplate='107'/>"; 
qry.Webs = "<Webs Scope='Recursive'/>"; 
DataTable resultTable = spWeb.GetSiteData(query);

As you can see, the SPSiteDataQuery object has Lists and Webs properties that need to be set.