Karine Bosch’s Blog

On SharePoint

CAML Designer for SharePoint 2013


Andy Van Steenbergen and I worked very hard to revamp the CAML Designer and finally the tool is ready to be released: here is the CAML Designer for SharePoint 2013. This version is much more user friendly and no hasle with resizing of the different panels anymore. The design is metro style based.

CD2013

The functionality of this version of the tool is similar to what it offers you for SharePoint 2010:

  • you can build CAML queries for single lists
  • you can build queries that can be executed with SPSiteDataQuery
  • beside the pure CAML queries, you can also get code snippets for the server-side object model, the .NET client-side object model, the JavaScript client-side object model and last but not least code snippets when working with REST.

With this first version of the tool you are not able to connect to SharePoint 2013 online yet, but it is definitely something we are going to look at for next version.

You can download the tool from the BIWUG site. Click the Downloads tab and select the CAML Designer. From here you will be able to download the CAML Designer.

When the CAML Designer is launched, you’ll find a menu button bar at the top right corner of the main screen. You can click the download button to check if a click-once update is available or not.

CD2013 update button

Normally the click-once deployments will notify you when an update is available or not, but we also see that people download the offline package as well.

The offline package is also a bit different. Now it contains the .application file and “Application Files” directory as well. So you can install CamlDesigner now instead of just run the exe.

Login

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

Caml Designer 2013

Before you can start working with the CAML Designer, you have to connect to a SharePoint site. Click on the connection button to get the connection panel:

CD2013 connection button

You can connect to following SharePoint sites:

  • a SharePoint 2010 site
  • a SharePoint 2013 site
  • an O365 site

Connecting to SharePoint 2013

To connect to a SharePoint 2013 site, click the Connection button at the top right menu of the main form. This will open a panel where you can define to which SharePoint site to connect. The SharePoint 2013 button is selected by default. You can specify your URL and choose how to connect:

  • using the server-side object model when the CAML Designer is installed on the machine running SharePoint 2013
  • using the .NET client-side object model when the CAML Designer is installed remotely
  • using the plain old web services of SharePoint

Toggle the Current Credentials button if you want to enter credentials.

Clicking the Connect button will establish a connection with the SharePoint site of your choice.

CAML Designer 2013

When you enter a URL, a small pre-check is done. If you entered a wrong URL, meaning a URL that can’t be found by the CAML Designer, you get following error message:

Connection UrlIsWrong

If you enter credentials that cannot be accepted by the SharePoint site you want to access, you get the following error message:

Connection IncorrectCredentials

If you already connected to the SharePoint site, you can choose the tile from the history list, representing the saved connection.

When the CAML Designer 2013 was able to connect to the specified site, the treeview in the left panel is populated with the lists of your SharePoint site. A spinning wheel is visible as long as the retrieval process runs and a status message in the bottom right corner of your screen informs you that a connection is being established.

Connecting to SharePoint 2010

You can also use the CAML Designer 2013 to connect to a SharePoint 2010 environment. In that case select the SharePoint 2010 button. You will see that you can only connect using the client-side object model or the web services.

connect to SP2010

Because the CAML Designer 2013 is compiled against the .NET framework 4.5, you are not able to connect to SharePoint 2010 with the server object model BUT the code snippets for the server-side object model remain available.

Once connected to the SharePoint 2010 environment, you will only get the code snippets that are available on SharePoint 2010; i.e. no REST snippets and limited set of code snippets for the client-side object model (which are lot extended in SharePoint 2010).

Connecting to Office 365

You can also connect the CAML Designer to your Office 365 environment and build your queries. Specify your URL to your environment and your credentials to log on and click the Connect button:

Connect to 0365

You can only connect to Office 365 environments that are already upgraded to SharePoint 2013. O365 that still have SharePoint 2010 underneath, are not supported by this tool.

If you would encounter problems when connecting to O365 sites, and you wish our support, you can do the following:

1) navigate to your site and append /_vti_pvt/service.cnf at the end of the url, i.e. https://TENANT.sharepoint.com/_vti_pvt/service.cnf.

2) Send the response to camlfeedback@biwug.be

This way we can try to find out what’s going wrong.

In the Recent list your O365 environment(s) are represented by a key icon.

When hoovering over the panes in the Recent list, you can see a tooltip informing you on how you connected to this site before:

recent connection tooltip

Clearing the history list

If you think that the list of available recent connections becomes too long or if there are a lot of obsolete connections in it, you can clear this history list.

Click the settings button on the menu bar:

CD2013 settings button

There you have the possibility to clear the list of recent connections:

clear history

Start to build queries

Expand the treeview to see all the lists:

SharePoint lists treeview

In this version of the CAML Designer, there is no ribbon anymore. The right pane is divided in two parts: an upper part where you can construct your query and a lower part where you can view the CAML query. You can also view code snippets for the different object models with which you can query SharePoint lists.

To construct your query, you can toggle the tabs in the upper panel:

  • 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.

To be able to construct a CAML query, you have to select a list from the treeview. Each tab contains a panel that gets populated with available fields from the selected list.

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.

The lower panel contains tabs to list the different code snippets based on the CAML query that is constructed in the upper panel. Following code snippets are provided:

  • The Caml tab shows you the pure CAML query.
  • The Server OM tab will show you a code snippet using the server-side object model to execute your CAML query.
  • The CSOM .NET tab will contain a code snippet that you can use when developing f.e. a WPF application that needs to connect to a SharePoint site.
  • The CSOM Rest tab will contain a code snippet that retrieves data from SharePoint using REST.
  • The web services tab will display a code snippet that executes the CAML query using the lists.asmx web service.
  • The PowerShell tab will display a code snippet that executes the CAML query using PowerShell.

No other types of code snippets are available yet. Code snippets are only provided in C#.

Hidden fields

In some cases you want to build a filter based on a value in a hidden field. By default the CAML Designer doesn’t show hidden fields. To solve this issue we added the check box “Show hidden fields” to the user interface just above the list treeview. By default the hidden fields are not displayed, but you can click this check box to get them displayed.

Show hidden fields

Long Display Names

One of our users als reported that there were issues with long display names. It would ask a lot of redesign to have this properly displayed so we chose to add a tooltip that shows the complete display name. We hope that this suits your needs.

Long display names

The OrderBy clause

If you need a sorted result set, you have to define a sort order. To define a sort order  for your query, you have to click the Order By tab in the upper panel.

Click the field in the left panel on which you want to sort and drag it to the right of the panel. 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.

CD2013 OrderBy

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 Last Name field is added, the CAML looks like the following:

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

While the available list fields are displayed with their display name, the internal name of the field is used to build the OrderBy clause.

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='Company' /> 
</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='Company' Ascending='FALSE'/> 
</OrderBy>

If you want to remove a field from the order by clause, drag it back to the left panel.

You can also change the order of the selected fields, you could for example drag and drop the Company tile to the top of the list; the CAML snippets will automatically be updated.

Unlike the CAML Designer for 2010, the query is not constantly executed to show you the results of the query you are building. Whe removed this functionality because a lot of developers encountered performance problems. If you want to see the result of your query, you can click the Execute button at the top right corner of the screen:

CD2013 execute button

Or click the Test tab next to the Query Options tab:

CD2013 test tab

Your results will be displayed in a grid:

CD2013 OrderBy results

The Test tab displays also a label that indicates the number of rows that are returned by the result set. Once the number becomes visible, you know that the retrieval has finished. This is handy when your query does not return a result and no grid becomes visible. The number zero will indicate that the query is finished but that no results have been found.

In the bottom pane you can inspect the CAML query and the different code snippets. By default the CAML tab is activated. Click the other tabs to inspect the different code snippets.

CD2013 code snippets

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("Developers");
if (spList != null)
{   
    SPQuery qry = new SPQuery();
    qry.Query= @"<OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" 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("Developers"); 
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="Company" Ascending="FALSE" /></OrderBy>
            </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

In this version of the CAML Designer, also code snippets for REST are generated:

.ajax({     
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title,Company desc", 
   type: "GET",     contentType: "application/x-www-url-encoded", 
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

As you can see in the screenshot, you can choose if the results need to be returned in JSON format or in ATOM format:

CD2013 code snippet rest

If you choose to have results returned in JSON format, you have to set the request header to ….

.ajax({
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title,Company desc", 
   type: "GET",      
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

If you choose to have results returned in ATOM format, you have to set the request headers as follows:

.ajax({     url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title,Company desc", 
   type: "GET",      
   contentType: "application/atom+xml;type=entry",
   headers: {"accept": "application/atom+xml"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

The difference lays in the way the results are returned in the response. If you sent your request for json, the results are returned as follows:

CD2013 rest atom response

If you sent your request for atom, the results are returned as follows:

CD2013 rest json response

If you need to pass your CAML query throught the plain old 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="Company" 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

As explained a few paragraphs before, you can click the Execute button to view the results of your CAML query.

CD2013 execute button

You can change the CAML manually in the text box in the lower pane and clicking this execute button will show you the results of your manually adapted query. The button will be disabled when you are in the other tabs with the code snippets.

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.

CD2013 viewfields

If you want to remove a field from the ViewFields clause, drag it back to the left panel.

You can also change the order of the selected fields, you could for example drag and drop the Job Title tile to the top of the list, it will automatically change the CAML snippets.

Your CAML query looks like the following:

<ViewFields>
    <FieldRef Name='Title' />
    <FieldRef Name='FirstName' />
    <FieldRef Name='Company' />    
</ViewFields>

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

SPList spList = spWeb.Lists.TryGetList("Developers");
if (spList != null)
{   
    SPQuery qry = new SPQuery();
 qry.ViewFields= "<FieldRef Name="Title" /><FieldRef Name="FirstName" /><FieldRef Name="Company" />"; 
    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("Developers"); 
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="FirstName" /><FieldRef Name="Company" /> 
 </ViewFields>
           <Query>
                <OrderBy><FieldRef Name="Title" /><FieldRef Name="Company" Ascending="FALSE" /></OrderBy>
            </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

The REST snippet will look as follows:

.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?
      $select=Title,Company,FirstName,JobTitle&$orderby=Title,Company desc", 
   type: "GET",      
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

The PowerShell snippet shows the following:

$spweb = get-spweb http://your-site 
 $splist = $spweb.Lists.TryGetList("Developers") 
if ($splist) 
{ 
   $query = New-Object Microsoft.SharePoint.SPQuery; 
   $query.ViewFields = "<FieldRef Name='FirstName' /><FieldRef Name='Title' /><FieldRef Name='Company' /><FieldRef Name='JobTitle' />"; 
   $query.ViewFieldsOnly = $true; 
   $items = $splist.GetItems($query); 
}

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="Company" Ascending="FALSE" /></OrderBy>"; 
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='FirstName' /><FieldRef Name='Company' />"; 
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = 
   listsWS.GetListItems("Developers", 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 tab on the upper panel and drag a field from the left panel to the right panel. The field control now consists of 3 parts: the field name, an operator button and a control in which you can specify a value.

CD2013 where clause

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.

CD2013 change 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.

A simple Where clause in CAML looks like this:

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

Boolean fields

There was also a problem with querying boolean fields. This issue is now solved:

  <Where>
      <Eq>
         <FieldRef Name='VeryTall' />
         <Value Type='Boolean'>1</Value>
      </Eq>
   </Where>

I tested and retested, and this query returns rows:

Boolean field query result

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.

CD2013 choice field

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'>Choice 2</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.

CD2013 lookup field

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'>France</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>

For the .NET client object model, the snippet will look as follows:

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

But the REST snippet is a bit different:

.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Cities')/Items?
      $expand=Country/Title&$filter=Country/Title eq 'France'", 
    type: "GET",      
    headers: {"accept": "application/json;odata=verbose"},
    success: onDataReturned;
    error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

The construction of the REST call is explained in more details in the blog post of Andrew Connell “Applying Filters to Lookup Fields with the SharePoint 2013 REST API“.

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.

CD2013 datetime field

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:

CD2013 datetime field 2

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.

CD2013 datetime field 3

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

 <Where>
     <Lt>
        <FieldRef Name='StartDate'/>
        <Value Type='DateTime'>2012-12-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:

CD2013 datetime field 4

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-12-17T10: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-12-17T10:30:00Z</Value></Lt></Where>
             </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

And the REST snippet looks as follows:

.ajax({     url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle(Tests)/Items?
       $select=Title,StartDate&$filter=StartDate lt '12/05/2012 10:30:00'", 
   type: "GET",      
   headers: {"accept": "application/json;odata=verbose"},
   success: onDataReturned;
   error: onError;
function onDataReturned(data){ // TODO: handle the data }
function onError(err) { // TODO: handle the error }

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);

User fields and UserMulti fields

When you choose a User field or UserMulti field to filter on, you can choose between filtering on the current user, on a specific user, or on membership (read the blog post of Christopher Clementen where he clearly explains how the <Membership> element works).

User field

If you choose to filter on the current user, your query will look as follows:

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' />
         <Value Type='Integer'>
            <UserID />
         </Value>
      </Eq>
   </Where>

If you choose to filter on a specific user then you can enter or the name of the user or its ID. If you enter the name of a user, your query looks as follows.

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' />
         <Value Type='User'>Karine Bosch</Value>
      </Eq>
   </Where>

If, at the other side, you want to filter on the user ID, the following query is generated, indicating that you are passing an ID:

   <Where>
      <Eq>
         <FieldRef Name='AssignedTo' LookupId='True' />
         <Value Type='Integer'>4</Value>
      </Eq>
   </Where>

If you choose to filter on Membership, you get an additional dropdown where you can select one of the following options:

  • CurrentUserGroups: this will retrieve all tasks that are assigned to groups to which the current user belongs.
  • SPWeb.Groups: this will retrieve all tasks that are assigned to groups.
  • SPWeb.AllUsers: this will retrieve all tasks that have been assigned to users and not to groups.
  • SPWeb.Users: this will retrieve all tasks that have been assigned to users that don’t belong to a specific group but that have been granted access to the site directly.
  • SPGroup: this will retrieve tasks that have been assigned to a specific group.

User field - membership

We’ve also foreseen a small description field that explains the selected option:

User field - membership CurrentUserGroups

When selecting the SPGroup option, another additional dropdown becomes available. This dropdown will list all groups:

User field - membership SPGroup

Selecting a group from this dropdown will retrieve all tasks that have been assigned to the selected group. The CAML query looks like the following:

   <Where>
      <Membership Type='SPGroup' ID='5'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

When you select the CurrentUserGroups option, the CAML query will look as follows:

  <Where>
      <Membership Type='CurrentUserGroups'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

For the SPWeb.Groups option, the CAML query looks like this:

  <Where>
      <Membership Type='SPWeb.Groups'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

For the SPWeb.AllUsers options, the following CAML query is generated:

  <Where>
      <Membership Type='SPWeb.AllUsers'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

And when you select the SPWeb.Users option, you will get the following CAML:

  <Where>
      <Membership Type='SPWeb.Users'>
         <FieldRef Name='AssignedTo' />
      </Membership>
   </Where>

Membership queries are a special case for REST requests; in this case the REST request needs to pass the CAML query itself. Additionally this REST request needs to be executed as a POST. This is well described in this blog post of CleverWorkarounds. Such a REST snippet generated by the CAML Designer looks like this:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Tasks')/GetItems(query=@v1)?@v1={\"ViewXml\":\"<View><Query><Where><And><Neq><FieldRef Name='Status' /><Value Type='Choice'>Completed</Value></Neq><Membership Type='SPWeb.Groups'><FieldRef Name='AssignedTo' /></Membership></And></Where></Query></View>\"}", 
   type: "POST", 
   headers: { 
         "X-RequestDigest": $("#__REQUESTDIGEST").val(), 
         "Accept": "application/json;odata=verbose", 
         "Content-Type": "application/json; odata=verbose" 
   }, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

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.

Taxonomy Field

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:

CD2013 taxonomy popup

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='Technology' />
        <Value Type='TaxonomyFieldType'>SharePoint 2010</Value>
     </Eq>
 </Where>

The results grid looks like this in my case:

CD2013 taxonomy test

Retrieve a Managed Metadata field by its ID

You can also retrieve list data based on the ID of a taxonomy field. The difficulty here is that a term has a name and an guid in the term store. You can retrieve list data based on a term name but this is dangereous as term names can change over time.

Taxonomy Field

When a term is used to tag a list item, this term is stored in a hidden list on site collection level. At this time, the term gets an ID, which is stored internally as WssId. Select the Query by ID option to indicate that you want to filter on the WssId of the term. The value in the textbox will immediately be changed into its ID.

Taxonomy Field by ID

And also the query instantly changes as follows:

 <Where>
    <Eq>
       <FieldRef Name='Technology' LookupId='True' />
       <Value Type='Integer'>1</Value>
    </Eq>
 </Where>

It will retrieve the same list items as when you would have queried on the term “SharePoint”.

Multi-select Managed Metadata fields

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

CD2013 taxonomy multi popup

This results in the following CAML query:

 <Where>
     <Eq>
        <FieldRef Name='MultiTechnology' />
        <Value Type='TaxonomyFieldTypeMulti'>Dev;IT Pro</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.

CD2013 taxonomy multi in operator

This translates in the following CAML code:

 <Where>
      <In>
         <FieldRef Name='MultiTechnology' />
         <Values>
            <Value Type='TaxonomyFieldTypeMulti'>Dev</Value>
            <Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value>
         </Values>
      </In>
   </Where>

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

The snippet for the server-side object model looks like this:

SPList spList = spWeb.Lists.TryGetList("CAMLTestData"); 
if (spList != null) 
{ 
   SPQuery qry = new SPQuery(); 
   qry.Query = 
   @"   <Where>
      <In>
         <FieldRef Name='MultiTechnology' />
         <Values>
            <Value Type='TaxonomyFieldTypeMulti'>Dev</Value>
            <Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value>
         </Values>
      </In>
   </Where>"; 
   qry.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='MultiTechnology' />"; 
   SPListItemCollection listItems = spList.GetItems(qry); 
}

The code snippet for the client-side object model looks like the following:

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("CAMLTestData"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery();
if (spList != null && spList.ItemCount > 0) 
{
    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml =
       @"<View>
              <Query>
                <Where>
                   <In>
                       <FieldRef Name='MultiTechnology' />
                       <Values>
                           <Value Type='TaxonomyFieldTypeMulti'>Dev</Value>
                           <Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value>
                       </Values>
                   </In>
                </Where>
             </Query>
             <ViewFields>
                 <FieldRef Name='Title' />
                 <FieldRef Name='MultiTechnology' />
             </ViewFields>
       </View>"; 
    ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
 }

Taxonomy fields are a special case for REST requests; in this case the REST request needs to pass the CAML query itself. Additionally this REST request needs to be executed as a POST. This is well described in this blog post of CleverWorkarounds. Such a REST snippet generated by the CAML Designer looks like this:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/GetItems(query=@v1)?"
        + "@v1={\"ViewXml\":\"<View><ViewFields><FieldRef Name='Title' /><FieldRef Name='FirstName' /><FieldRef Name='Company' /></ViewFields>"
        + "<Query><Where><Eq><FieldRef Name='Technology' /><Value Type='TaxonomyFieldType'>SharePoint</Value></Eq></Where></Query></View>\"}", 
   type: "POST", 
   headers: { 
         "X-RequestDigest": $("#__REQUESTDIGEST").val(), 
         "Accept": "application/json;odata=verbose", 
         "Content-Type": "application/json; odata=verbose" 
   }, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

The snippet for the web services gives you this:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); 
XmlNode queryNode = doc.CreateElement("Query"); 
queryNode.InnerXml = 
     "<Where><In><FieldRef Name='MultiTechnology' /><Values><Value Type='TaxonomyFieldTypeMulti'>Dev</Value>"
   + "<Value Type='TaxonomyFieldTypeMulti'>IT Pro</Value></Values></In></Where>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields"); 
viewfieldsNode.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='MultiTechnology' />";
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("CAMLTestData", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

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.

The FileRef field

There is also a section below that details how to build queries to retrieve documents but there are cases that a simple query does the trick. In that case the users prefer to stay on the Where tab and to build a query based on the FileRef field. When you drag and drop the FileRef field on the Where tab, you get a lookup displaying all folders in the selected document library.

FileRef field

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.

CD2013 multiple where and

Click on the control to toggle it to Or.

CD2013 multiple where or

In CAML it looks as follows:

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

You can add as many filter criteria as need.

CD2013triple where

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

 <Where>
      <Or>
         <And>
            <Eq>
               <FieldRef Name='MultiCountries' />
               <Value Type='LookupMulti'>Belgium</Value>
            </Eq>
            <Eq>
               <FieldRef Name='MultiCountries' />
               <Value Type='LookupMulti'>Luxembourg</Value>
            </Eq>
         </And>
         <Eq>
            <FieldRef Name='Title' />
            <Value Type='Text'>Test 1</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>

Remark REST snippets:

A where clause built up of multiple fields, does not seem to work with REST.

Remove a field

If you want to remove a field from the Where clause, just drag it back to the left fields list.

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.

Remark: Not all query options work with REST snippets. I’ll mention each time what works and what not.

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 and with REST.

Row Limit

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

CD2013 rowlimit

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(); 
}

And also REST requests can send the row limit to the server by adding $top=3 to the URL:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Developers')/Items?$orderby=Title&$top=3", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

For a detailed explanation of the other query options like, I refere to my other blog post on the CAML Designer for SharePoint 2010. These query options only work for the server object model and the web services. They don’t work for the client-side object model nor for REST requests.

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:

CD2013 all files and folders in rootfolder

The user interface of the CAML Designer gives you a wide range of options that you can configure.

CD2013 all files and folders query options

Clicking the first option “Query all files and folders in root folder” will result in an empty query as this is the standard behavior.

The same query would look like the following for the server-side object model:

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

The code snippet for the client-side object model for .NET looks as follows:

ClientContext clientContext = new ClientContext("your site");
 Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Documents");
 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='Modified' Ascending='FALSE' /></OrderBy>
             </Query>
       </View>"; 
   ListItemCollection listItems = spList.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
 }

And the web services code snippet is the following:

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

REST reacts a bit differently: if you send a normal request (as described above) to a document library, you will get all files and folders in all sub folders in return. You will find more information on REST in a future article.  If you want to query all files and folders in the root folder of a document library with REST, your request needs to look as follows:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Documents')/RootFolder", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

If you only want to retrieve the folders in 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“. The generated code snippets are the following:

Server-side object model:

SPList spList = spWeb.Lists.TryGetList("Documents"); 
if (spList != null) 
{ 
   SPQuery qry = new SPQuery(); 
   qry.Query = 
   @"   <Where>
      <Eq>
         <FieldRef Name='FSObjType' />
         <Value Type='Integer'>1</Value>
      </Eq>
   </Where>
   <OrderBy>
      <FieldRef Name='Modified' Ascending='FALSE' />
   </OrderBy>"; 
   SPListItemCollection listItems = spList.GetItems(qry); 
}

Client-side object model for .NET:

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

Web Services:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
XmlNode queryNode = doc.CreateElement("Query");
queryNode.InnerXml = "<Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where>"
        + "<OrderBy><FieldRef Name='Modified' Ascending='FALSE' /></OrderBy>";
XmlNode viewfieldsNode = doc.CreateElement("ViewFields");
XmlNode queryOptionsNode = doc.CreateElement("QueryOptions");
System.Xml.XmlNode items = listsWS.GetListItems("Documents", null, queryNode, viewfieldsNode, null, queryOptionsNode, null);

REST:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Documents')/RootFolder/Folders", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

Selecting the option “Query all files in root folder” generates this CAML:

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

To get files and folders from the root and its sub folders with CAML, you can set the ViewAttributes property. This property is not translated in REST because a standard REST request already returns files and folders from the whole folder structure of the queried document library. As this property works as before, I refer to my article CAML Designer for SharePoint 2010 for more details.

But you can also query 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:

CD2013 queroptions specific folder

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:

CD2013 queroptions specific folder 2

But the CAML query will only be constructed and executed after you selected one of the radio buttons 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 A</Folder>
 </QueryOptions>

This translates in 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 A"];
    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 A";

    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 A</Folder>"; 
System.Xml.XmlNode items = listsWS.GetListItems("Tests", null, queryNode, viewfieldsNode, 2, queryOptionsNode, null);

The REST code looks like:

$.ajax({ 
   url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/GetFolderByServerRelativeUrl('/Shared Documents/Folder A')", 
   type: "GET", 
   headers: {"accept": "application/json;odata=verbose"}, 
   success: function (data) { 
      if (data.d.results) { 
         // TODO: handle the data  
         alert('handle the data'); 
      } 
   }, 
   error: function (xhr) { 
      alert(xhr.status + ': ' + xhr.statusText); 
   } 
});

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);

And the REST request looks like this:

$.ajax({
     url: _spPageContextInfo.webAbsoluteUrl 
          + "/_api/web/GetFolderByServerRelativeUrl('/Shared Documents/Folder A')/Files",
     type: "GET",
     headers: {"accept": "application/json;odata=verbose"},
     success: function (data) {
        if (data.d.results) {
           // TODO: handle the data
            alert('handle the data');
        }
     },
     error: function (xhr) {
        alert(xhr.status + ': ' + xhr.statusText);
     }
  });

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);

This seems not to be possible with REST.

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>

Also here it seems not possible with REST.

58 Comments »

  1. Hi Karine,
    Nice work and tool. Using 2013 version, despite the smart screen filtering on first use, it throws an error when connecting using a Win8 Ent. Looks like an missing assembly ref.

    27/12/2012 14:12:49: LoginScreen: An error has occured while looking for the server assemblyReferência de objeto não definida para uma instância de um objeto. – em CamlDesigner2013.Connections.UI.ConnectionView..ctor() na c:\Development\CamlDesigner 2013\CamlDesigner2013\CamlDesigner2013\Connections\UI\ConnectionView.xaml.cs:linha 38

    Comment by Eduardo Bonato | December 27, 2012 | Reply

  2. Im getting the same error. Thoughts?

    Comment by Arno Nel | March 25, 2013 | Reply

  3. Hi Arno, did you install the tool recently? I remember there was indeed a missing assembly in the beginning but I though that was solved. In that case, downloading the new exe should do the trick. If not, let me know and I’ll check the installer.
    Karine

    Comment by Karine Bosch | March 25, 2013 | Reply

  4. Hi Karine. Long time🙂 Hope all is well.

    Downloaded last night. Using Windows 8.

    Comment by Arno Nel | March 25, 2013 | Reply

  5. […] Complete documentation on the CAML Designer 2013 can be found here. […]

    Pingback by New version of CAML Designer 2013 released! « Karine Bosch’s Blog | April 28, 2013 | Reply

  6. Unfortunately I ran across the same issue:
    LoginScreen: An error has occured while looking for the server assembly
    Object reference not set to an instance of an object. – at CamlDesigner2013.Connections.UI.ConnectionView..ctor()

    Comment by Mierzej | June 11, 2013 | Reply

  7. Hi Karine,

    This is a lovely tool, but apparently, I am not able to make it run in my machine. I am running a windows 8 and I am connecting to my Ofiice 365 Sharepoint site, after clicking the connect button, the application crashes. Not sure what happens, hope you give me some thoughts on this, thanks.

    Comment by Isaac | June 18, 2013 | Reply

  8. According to all the people’s comments, just like other users I find it a really handy and appealing tool, but unfortunately with a one, major shortcoming – exception handling. I was able to solve my “An error has occurred while looking for the server assembly” by attaching the Visual Studio debugger, the exception I caught was quite comprehensive.

    Comment by Mierzej | June 18, 2013 | Reply

    • Do you mind sharing that exception? We could try to do something about it in next version.
      Thanks!

      Comment by Karine Bosch | June 18, 2013 | Reply

  9. Hi Karine,
    I have installed on WIndows Server 2008 and on Windows Server 2012 but it crashes when attempting to connect to any site with the following problem details:
    Problem signature:
    Problem Event Name: CLR20r3
    Problem Signature 01: camldesigner2013.exe
    Problem Signature 02: 1.0.0.1
    Problem Signature 03: 517a099d
    Problem Signature 04: System
    Problem Signature 05: 4.0.30319.18021
    Problem Signature 06: 506a72b7
    Problem Signature 07: 21ae
    Problem Signature 08: 61
    Problem Signature 09: System.Net.WebException
    OS Version: 6.1.7601.2.1.0.272.7
    Locale ID: 1033
    Additional Information 1: 4362
    Additional Information 2: 4362e2d7d06552f3478ca44252bbdd97
    Additional Information 3: a2ba
    Additional Information 4: a2baf2b82a8c81a9b96e387f8368468e

    Read our privacy statement online:
    http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

    If the online privacy statement is not available, please read our privacy statement offline:
    C:\Windows\system32\en-US\erofflps.txt

    Comment by Earl Libby | June 22, 2013 | Reply

    • Running with debugger seems like this is site not found (404) error — successfully connected to my local sharepoint 2010 system but not with a valid Office/365 site (cut and pasted the URL so I am sure its corrent — for the Office/365 URL I am getting thru Visual Studio debugger The remote server returned an error: (403) Forbidden. Double checked and I am sure the userid and password are correct

      Comment by Earl Libby | June 22, 2013 | Reply

      • Are you sure your Office 365 account is already upgraded to SP2013? The CAML Designer doesn’t support SP2010 version.
        Kind regards, Karine

        Comment by Karine Bosch | June 23, 2013

    • Weird. I Always test with my own version (in VS2012). I’ll download the latest version from the BIWUG site and test with that; you never know something went wrong during compilation.
      Karine

      Comment by Karine Bosch | June 23, 2013 | Reply

  10. Can we create query using joins in CAML designer?

    Comment by Amar | August 8, 2013 | Reply

    • Not yet, we foresee it for a future version.

      Comment by Karine Bosch | August 11, 2013 | Reply

  11. Offset is not working in caml query for sharepoint 2013

    Comment by damania | September 2, 2013 | Reply

    • Oh really? I’ll check this out tomorrow.
      Karine

      Comment by Karine Bosch | September 2, 2013 | Reply

  12. ViewFields>

    I am trying to fech data from discussion forum,the last 100 days count.but my query fails it shows all the records instead of last 100.I am working on sharepoint 2013 community template.Any help will be appreciated.Thanks

    Comment by damania | September 4, 2013 | Reply

    • It looks like this is a bug. I’ll add it to the list of bugs and it will be handled with the next release.
      Kind regards,

      Comment by Karine Bosch | September 4, 2013 | Reply

  13. Is there a way to write a query to fetch last 7 days or 100 days records..

    Comment by harsh damania | September 13, 2013 | Reply

  14. Can’t seem to get this to work with Office365/sharepoint.com. I have tried 3 sites (all 2013 versions) and all fail at credentials: “INSUFFICIENT DATA Please, check the credentials to your SharePoint site they seem incorrect.”

    Anyone have success with this?

    Comment by visioinc | September 15, 2013 | Reply

  15. Hey there. Great tool once you get to know it🙂
    I’m missing some fields in my where clause (contenttypeid, publishing start date, publishing expiration date, …). Is there a way I can you them to build my query? Also I’d prefer if you used the actual sharepoint internal name instead of some custom nam (e.g. FileRef).

    Comment by RS | September 26, 2013 | Reply

    • Hi RS, thanks for trying this tool out!
      In response to your questions:
      1) Hidden fields are never shown in the list of available fields. Do you think you need those in your query?
      2) We use the display names in the user interface (not some custom name invented by us). The CAML query itself uses the correct field names.
      Karine

      Comment by Karine Bosch | September 26, 2013 | Reply

      • Thank you for your quick response.
        Concerning #1: Most definitely. For example if you want to search for content types including derived content types you’ll probably want to search by FieldRef ID rather than FieldRef name. I’m fine with #2. Probably I just never came across the Field FileRef’s DisplayName🙂 Out of a developer’s perspective the internal field name comes more natural to me🙂

        Comment by RS | September 26, 2013

      • ok, I’ll add this to the list of enhancements: I’ll add a checkbox somewhere in the UI that you can check if you want to work with hidden fields.

        Comment by Karine Bosch | September 26, 2013

  16. Any chance we can query TaxonomyFieldMulti fields by TermGuid, not by label? Doesn’t work too well in multi lingual environments? I tried to learn from the designer but it uses the label as where filter and searching for a translated label doesn’t yield the expected results..

    Comment by RS | September 26, 2013 | Reply

    • Hi RS, at the moment the CAML Designer only supports the label. I’ll add your request to our list of bugs/enhancements. I’ll take a look at it during the weekend.
      Kind regards,
      Karine

      Comment by Karine Bosch | September 26, 2013 | Reply

      • Again thank you for your prompt response.I’d very welcome this feature🙂

        Comment by RS | September 26, 2013

  17. How do you save a CAML query to work on again later? I tried pasting the CAML back in but it strips it down to just ?

    Comment by Per | October 7, 2013 | Reply

    • There is no save functionality foreseen. I’ll put it on our list of requirements to work on for next release(s).

      Comment by Karine Bosch | October 7, 2013 | Reply

  18. Hi all,
    is possible to get a basic guide of how to implement the code that you get from CAMLDESIGNER inside of “Script Task Editor” with VB.net or VC# from SSIS?

    I have poor knowledge in VB or VC#.
    Any help is appreciated

    Comment by Cris | January 28, 2014 | Reply

    • You could use the c# snippets that the CAML Designer generates.
      Kind regards.

      Comment by Karine Bosch | January 28, 2014 | Reply

      • thanks

        Comment by Cris | January 28, 2014

  19. Hi all
    For some reason when I select these options to get only the files inside of the folder_Timesheet is not coming with any files result after select the ‘Query all files in this folder and its subfolder’.
    But I choose until ‘Query all files and folders all folders deep’ I can see all the files from the root folder and from the folder_Timesheet

    X*Include Mandatory columns
    X*Expand User Field
    X*Files and Folders Options
    -X-Query all files and folders all folders deep
    -X-Query sub folder
    –x–/Timesheet/folder_Timesheet
    –x—Query all files in this folder and its subfolder

    I am selecting something wrong or is a kind glitch

    Comment by Carmen | January 28, 2014 | Reply

  20. For some reason when I select these options to get only the files inside of the sub_folder is not coming with any result.

    X*Include Mandatiory columns
    X*Expand User Field
    X*Files and Folders Options
    -X-Query all files and folders all folders deep
    -X-Query sub folder
    –x–/Timesheet/folder_Timesheet
    –x—Query all files in this folder and its subfolder

    Am I selecting something wrong or it is a king of glitch?
    kind regards,

    Comment by Carmen | January 28, 2014 | Reply

    • Currently, we are bug fixing to prepare for a new release one of the coming weeks. I’ll add your remarks to the bug list and take a look at it.
      Kind regards,
      Karine

      Comment by Karine Bosch | January 29, 2014 | Reply

    • Carmen,
      Because you want to execute the query on a sub folder, can you unselect “query all files and folders all folders deep”? I think that will solve your issue.
      Karine

      Comment by Karine Bosch | January 29, 2014 | Reply

  21. Hello, First I would like to say that the tool is great and helps a lot during development. Unfortunately I have probably found a bug which is related to lookup fields. We have a list containing fields like AssignedTo, RequestedBy etc. which are in fact Lookup fields. When I create a query in the desiner (section where) fields are defined as Multiuser and query retrieves no results. If I redefine the fields in query as lookup, it works, e.g.

    150

    Comment by Pavel Cermak | February 5, 2014 | Reply

    • Unfortunately, the code snippet cannot be inserted unfortunately:/ So I have encoded the code:

      <Where><Eq><FieldRef Name='AssignedTo' LookupId='true'/><Value Type='Lookup'>150</Value></Eq></Where>

      Comment by Pavel Cermak | February 5, 2014 | Reply

      • Hi Pavel,
        Thanks for your feedback. I added your remark to the list of bugs. We are currently working on a new release that should come out one of the coming weeks. I’ll try to solve your issue in the meantime.
        Kind regards,
        Karine

        Comment by Karine Bosch | February 9, 2014

  22. Hi Karine,

    Thanks for the articles and CAML designer. Is there any update on new version of CAML designer ?
    Also, are you planning to write articles on your 2013 experiences ? That will be really helpful ..Looking forward for your 2013 articles…

    Thanks…

    Comment by Nimisha | April 22, 2014 | Reply

    • Hi Nimisha,
      Thanks for your nice comment! And yes, there will be an update soon with a bug fix on Taxonomy fields and an additional CAML element to retrieve users and groups.
      Karine

      Comment by Karine Bosch | April 22, 2014 | Reply

  23. Do you plan to add support for GroupBy expression?

    Comment by Rylov Roman (@Rylov_Roman) | October 17, 2014 | Reply

    • Good point! Yes, we are working on it. Next version will be released beginning of November.
      Karine

      Comment by Karine Bosch | October 18, 2014 | Reply

  24. I have recently created CAML Query designer based on my own requirements and I am giving that as an utility for free. Hope this will help SharePoint developers in better way.

    http://praveenbattula.blogspot.com/2015/02/download-caml-query-designer.html

    Comment by Praveen | February 18, 2015 | Reply

  25. This was immensely useful to me in creating web service queries for a specific subfolder on a document library. Thank-you.

    Comment by Andrew Gaskell | October 27, 2015 | Reply

  26. […] CAML Designer for SharePoint 2013 […]

    Pingback by Caml Designer 2013 | CamlDesigner | January 12, 2016 | Reply

  27. Hi Karine.
    It has been a while.🙂 Just want to drop in to report two minor bugs. Nothing serious, but you might want to know they are there:
    1. When changing the order direction of a field, the entry gets duplicated instead of just changing the “ascending”-property, causing something like this:

    2. The Help buttons sends me to a 404 – Site

    Greetings from Hamburg,
    Ole

    Comment by Ole Albers | March 22, 2016 | Reply

    • Ok. WordPress filtered the Code. This is how it looks like: http://pastebin.com/9kNaztxS

      Comment by Ole Albers | March 22, 2016 | Reply

      • Hi Ole,
        Thanks for reporting these bugs! I’ll add them to the bug list.
        Kind regards,
        Karine Bosch

        Comment by Karine Bosch | March 30, 2016

  28. Hi Karine,
    Just wondered, has any one contacted you saying they can’t get it to connect to their Sharepoint sites? I have this problem, and although it think it likely to do with my IT Dept., i thought i would check. The domain i am trying to get it to connect to, using CSOM, is https://staff.derby.ac.uk/sites/committees
    which is on our intranet. I have put my credentials in but can;t get a connections, it just tells me the “Check the URL to your Sharepoint site, it seems incorrect”

    Comment by Chris | November 23, 2016 | Reply

    • Hi Chris,
      Stupid question, but I suppose you have access to this SharePoint site. In general all are users are able to connect to their SharePoint sites using CSOM. You can also try through the web services to make sure.
      Kind regards,
      Karine Bosch

      Comment by Karine Bosch | November 28, 2016 | Reply

  29. Hi Karine,

    Not at all –🙂
    I do have access – I’ve got full control access. It’s certainly puzzling me why I can’t connect. I’m trying to get our IT Dept to find a solution, but not got there yet.
    Thanks for the reply

    Chris

    Comment by Chris Davies | November 28, 2016 | Reply

    • You can try to create a console app with Visual Studio and connect to your SharePoint site using CSOM:

      ClientContext context = new ClientContext(siteURL);
      Web web = context.Web;
      SecureString pwd = new SecureString();
      foreach (char c in password.ToCharArray()) pwd.AppendChar(c);
      context.Credentials = new NetworkCredential(netUserName, pwd);

      Does this work?
      Karine

      Comment by Karine Bosch | November 28, 2016 | Reply


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: