Introduction
Writing CAML by hand (typing) is always supposed to be painful. There are many tools available in online for doing this though doveloper must type it by his/her own. Tools just help to select query operators and debug. There is no drag and drop option or UI for building whole query. In this article, I will show how we can build CAML Query without typing. I can assure you that it must be optimized than typing by own. Target audiences those who are already familiar with CAML and creating list view.
CAML Query usages
We can use CAML Query everywhere like SOM (Sever Object Model), CSOM (Client Object Model), JSOM (Javascript Object Model and REST API also. Wherever we use it, the syntax is always same. Let's recall our code..
Sever Side Object Model
var spQuery = new SPQuery();
spQuery.Query = string.Concat("CAML Query goes here");
var listItems = spWeb.Lists["List Name"].GetItems(spQuery);
Client Object Model
CamlQuery query = CamlQuery();
query.ViewXml = string.Concat("CAML Query goes here");
var listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
Javascript Object Model
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml('CAML Query goes here');
this.collListItem = oList.getItems(camlQuery);
clientContext.load(collListItem);
REST API SharePoint 2013
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getbytitle('List Name')/GetItems",
type: "POST",
headers: {
"accept": "application/json;odata=verbose",
"X-RequestDigest": $("#__REQUESTDIGEST").val(),
"content-Type": "application/json;odata=verbose"
},
data: JSON.stringify({
query : {
__metadata: {
type: "SP.CamlQuery"
},
ViewXml: 'CAML Query goes here'
}
}),
success: function (data) {
console.log(data);
},
error: function (error) {
alert(JSON.stringify(error));
}
});
Now we will see how we can build our CAML query without typing by hands.
CAML Building in Action
The approach is very straight forward. We will just create a list view and get our CAML Query from it. Lets see it in action.
1. Go to the list settings and click Create View
2. Select Standard View, type View Name and choose Public View
3. Select Columns those you want in CAML Query
4. Select Column Name and choose ascending or descending option for Ordering from Sort section
This is the equivalent of <OrderBy> Element
<OrderBy>
<FieldRef Name="Field Name"/>
<FieldRef Name="Field Name" Ascending="FALSE"/>
<FieldRef Name="Field Name" Ascending="TRUE"/>
</OrderBy>
5. Now select Column Name, Contition and choose Value from Filter section 
This equivalent of <Where> Element
<Query>
<Where>
<Geq>
<FieldRef Name="Field Name"/>
<Value Type="DateTime">
<Today/>
</Value>
</Geq>
</Where>
<OrderBy>
<FieldRef Name="Field Name"/>
</OrderBy>
</Query>
Let's see some equivalent conditions between View and CAML
| Condition
| Equivalent CAML Query
|
| is equal to
| <Eq>
|
| is not equal to
| <Neq>
|
| is greater than
| <Gt>
|
| is less than
| <Lt>
|
| is greater than or equal to
| <Geq>
|
| is less than or equal to
| <Leq>
|
| begins with
| <BeginsWith>
|
| contains
| <Contains>
|
For checking null just keep the value as blank
This is equivalent to <IsNull> and <IsNotNull> Element
<Or>
<IsNull><FieldRef Name="Employee" /></IsNull>
<IsNotNull><FieldRef Name="ID" /></IsNotNull>
</Or>
6. Now you can go for Group By if it is needed from Group By section
This is equivalent to the GroupBy Element
<GroupBy Collapse="TRUE" GroupLimit="30">
<FieldRef Name="Title" />
</GroupBy>
Obtaining CAML Query from view
CAML query can be obtained from view in several ways. My preferred way is PowerShell. So let's start with PowerShell. Open PowerShell and paste following scripts and that will yield our CAML query in a text file.
$spWeb = Get-SPWeb -Identity "Site URL goes here";
$spList = $spWeb.Lists["List Name"];
$spView = $spList.Views["View Name"];
$spView.Query | Out-File "Path with file name"
Now open the text file and use it in SOM, CSOM, JSOM or REST API
If you are not comfortable with PowerShell, you can use any rest client like Advanced Rest Client for Chrome or fiddler. Below example demonostrate Advanced Rest Client. This example is applicable for SharePoint 2013.
API endpoint is
http://Site URL/_api/Web/Lists/getbytitle('List Name')/Views/getbytitle('View Name')?$select=ViewQuery
Headers will be Accept: application/json;odata=verbose to get result as JSON
Now click on Send button and that will return the result as JSON like following
{
"d": {
"__metadata": {
"id": "http://site url/_api/Web/Lists(guid'ec945846-bea2-4d3d-ba02-6e9f6dea9541')/Views(guid'91eb23c1-b489-4eb6-9f9a-571c32db6a4f')",
"uri": "http://site url/_api/Web/Lists(guid'ec945846-bea2-4d3d-ba02-6e9f6dea9541')/Views(guid'91eb23c1-b489-4eb6-9f9a-571c32db6a4f')",
"type": "SP.View"
},
"ViewQuery": "<GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"Title\" /></GroupBy><OrderBy><FieldRef Name=\"ID\" /></OrderBy><Where><Or><IsNull><FieldRef Name=\"Employee\" /></IsNull><IsNotNull><FieldRef Name=\"ID\" /></IsNotNull></Or></Where>"
}
}
Just copy the value of ViewQuery from returned JSON and use it.
#Update
Advanced Rest Client has authentication problem in new version. It does not work seamlessly with SharePoint. So I made my own SP REST Client from Chrome. You can explore/test REST API using it very easily. Documentation of my SP REST Client can be found here.
The CAML query can be obtained via REST API and jQuery in SharePoint 2013. You can go through my another article about REST API here. Paste following code in the console of your browser if jQuery is included in your site.
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getbytitle('list name')/Views/getbytitle('view name')?$select=ViewQuery",
type: "GET",
headers: {
"accept": "application/json;odata=verbose",
},
success: function (data) {
console.log(data.d);
},
error: function (error) {
alert(JSON.stringify(error));
}
});
For SharePoint 2010, following method will help to get CAML query from view.
function getCamlQueryFromView(listTitle, viewTitle) {
var context = new window.SP.ClientContext.get_current();
var list = context.get_web().get_lists().getByTitle(listTitle);
var view = list.get_views().getByTitle(viewTitle);
context.load(view);
context.executeQueryAsync(
function(sender, args) {
console.log(view.get_viewQuery())
},
function(sender, args) {
alert("error: " + args.get_message());
}
);
}
Above code will work for SharePoint 2013 also. As approach varies from SharePoint version to version, I always preferred to use PowerShell.
So that's all and start building CAML in this way and let me know your feedback how it works!
Points of Interest
I must acknowledge that something I could not find equivalent in this way. If you can achieve it, please let me know in comment thereafter I will update my article. Some of those are mentioned below.
1. Membership Element
2. NotIncludes Element
3. In Element
4. DateRangesOverlap Element
5. and others
If you need above elements for complex CAML query, you have to type it by hand. My suggestion is: at first start with my approach, then modify it accordingly. I hope it will releive your 80% work.
