http://karinebosch.wordpress.com/my-articles/caml-designer-for-SharePoint-2013/
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.
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:
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:
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.
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:
If you enter credentials that cannot be accepted by the SharePoint site you want to access, you get the following error message:
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.
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:
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:
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:
There you have the possibility to clear the list of recent connections:
Start to build queries
Expand the treeview to see all the lists:
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.
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.
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.
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:
Or click the Test tab next to the Query Options tab:
Your results will be displayed in a grid:
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.
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("Developersf (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("DevelopersclientContext.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:
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:
If you sent your request for atom, the results are returned as follows:
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.
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.
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("Developersf (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("DevelopersclientContext.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("Developersf ($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.
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.
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:
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'>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.
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("DevelopersclientContext.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.
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-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:
You can view the code snippet for the Server object model:
SPList spList = spWeb.Lists.TryGetList("Testsif (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("TestscclientContext.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).
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.
We’ve also foreseen a small description field that explains the selected option:
When selecting the SPGroup option, another additional dropdown becomes available. This dropdown will list all groups:
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.
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='Technology' />
<Value Type='TaxonomyFieldType'>SharePoint 2010</Value>
</Eq>
</Where>
The results grid looks like this in my case:
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.
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.
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:
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.
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("CAMLTestDataif (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("CAMLTestDataclientContext.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.
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>
<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.
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 theQueryOptions 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("Testsif (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.
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("TestscclientContext.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:
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.
The same query would look like the following for the server-side object model:
SPList spList = spWeb.Lists.TryGetList("Documentsif (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("DocumentsclientContext.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("Documentsif (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("DocumentsclientContext.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:
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 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("Sharedments");
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("TestscclientContext.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("Sharedments");
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("Sharedments");
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("Sharedments");
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("Sharedments");
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.
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.
Karine
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()
Thanks!
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
C:\Windows\system32\en-US\erofflps.txt
Kind regards, Karine
Karine
Karine
Kind regards,
If it is the case and you still encounter problems when connecting to O365 sites, 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.
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).
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
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 :)
Kind regards,
Karine
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?
Any help is appreciated
Kind regards.
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*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
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
kind regards,
Kind regards,
Karine
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
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
Also, are you planning to write articles on your 2013 experiences ? That will be really helpful ..Looking forward for your 2013 articles…
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