CAML Query Builder for SharePoint 2013/2016/2019/Online

In this sharepoint caml query builder tutorial, we will discuss CAML as well as the free CAML query builder tool in SharePoint 2013 and how we can use the U2U CAML query builder tool step by step. Here we will discuss how to CAML query builder for SharePoint 2013/2016/2019/Online. We can use CAML query builder for SharePoint Online and SharePoint on-premises versions.

We will see how to download and install CAML query builder for SharePoint 2016/2013/Online, how to use CAML query builder in SharePoint.

CAML Query Builder in SharePoint Online/2013/2016

CAML know as Collaborative Markup Language is an XML-based language for querying and updating SharePoint objects like List and Libraries. CAML helps us retrieving items from a SharePoint list based on various conditions, we can also put order by, can also limit items returns from the query etc.

The below is a simple syntax of the CAML query:

<Query>
<Where>
<Eq>
<FieldRef Name="FieldName" />
<Value Type="DataType">Value</Value>
</Eq>
</Where>
<OrderBy>
<FieldRef Name="FieldName" />
<FieldRef Name="FieldName" />
</OrderBy>
</Query>

Here we can use various other operators like below:

Comparison Operators	General Meaning
Eq	=
Gt	>
Lt	<
Geq	>=
Leq	<=
Neq	<>
Contains	Like
IsNull	Null
IsNotNull	NotNull
BeginsWith	Beginning with word
DateRangesOverlap	compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap

Rather than writing the query manually to query sharepoint list, we can use a free tool known as CAML query builder to generate the sharepoint caml query for us.

Advantages of CAML query builder tool

We learn what is CAML? And let us see what are the advantages of CAML query builder tool.

• We can manually write the CAML query for SharePoint, but when you will use the caml query builder, it will help us to build the query without a syntax error. Also, you can validate and see the result in the CAML query builder tool.
• The user interface is easier to create the query in the CAML query builder.
• CAML query builder is a free tool that you can download and install in a SharePoint server as well as a client operating system like Windows 10/8/7. From there you can connect to SharePoint Online sites.

Download & Install CAML Query Builder in SharePoint

SharePoint caml query builder is a great tool to create and test CAML queries in SharePoint. This will work with both on-premise as well as SharePoint Online. You should have Microsoft .NET Framework 4.5 installed in your machine else it will not work.

First Download the Tool from this URL and then install it. You can download “U2U Caml Query Builder for SharePoint 2013 (Standalone Edition)”.

The U2U Caml Query Builder for SharePoint will help us to query SharePoint list. Internally, this tool used the SharePoint client object model code to connect to SharePoint Online or 2013/2016 sites remotely.

How to use caml query builder

Now, we will see how to use caml query builder to connect to SharePoint On-premises as well as SharePoint Online sites.

Once installation over, when you will open the tool, it will open the Connect dialog box, through which you will be able to connect to your on-premise as well as your online environment. see fig below:

Here first I gave my on-premise site URL and when I connect it, it displays me all the list and libraries presented in the SharePoint site like below:

Similarly, you can also connect to a SharePoint Online site. When connecting to an online site, you have to provide the username and password. And then the URL.

Enter the URL like https://onlysharepoint2013.sharepoint.com/. If you will add /SitePages/Home.aspx, it will through an error. See fig below:

Here is my SharePoint Online, I have a list name as TestList and I want to build the query for that SharePoint list.

Building CAML Query using CAML Query Builder

Now, we will see how to build our CAML query using CAML query builder.

Now select the SharePoint Online List and then New query -> Query with View Fields as shown in the fig below

This will create two tabs one is Query and another one is View Fields. In the View Fields tab you can select the Columns which are you want to query using the calm query builder like below:

Here I have selected the ID, Title, and First Name SharePoint List columns and it showed me the CAML query in the Editor window.

Any time you can run the caml query and can see the results. It will also generate the CSOM Code and Server-side code for this which you see from the corresponding tabs.

Now let us go to the Query tab and add some filter criteria into the query.

Here I added a Filter that If Title Contains Items and then added Title equals Sharepoint item-1. You can add the Or or And condition by click on the Filter drop-down.

Add Order By in CAML Query

Similarly, If you want to add one Order by then you can click on the Add order by element button and then select the Column and the condition whether Ascending or Descending.

Then it will generate the CAML query for you in the Editor box.

If you want to use the same query in a CSOM code, then you can write like below:

using (ClientContext ctx = new ClientContext())
{
Web web = ctx.Web;
List list = web.Lists.GetById(new Guid("5c42def2-bd2c-4df6-bcca-359351944cd2"));
var q = new CamlQuery() { ViewXml = "<View><Query><Where><Or><Contains><FieldRef Name='Title' /><Value Type='Text'>Items</Value></Contains><Eq><FieldRef Name='Title' /><Value Type='Text'>Sharepoint item-1</Value></Eq></Or></Where><OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy></Query><ViewFields><FieldRef Name='ID' /><FieldRef Name='First_x0020_Name' /><FieldRef Name='LinkTitle' /></ViewFields><QueryOptions /></View>"};
var r = list.GetItems(q);
ctx.Load(r);
ctx.ExecuteQuery();
}

CAML Query filter by date range is not working in SharePoint 2013 JavaScript client object model

Let us see, how to use CAML based on the created date range in SharePoint 2013/2016/Online. How to use caml query between two dates in SharePoint Online/2013.

CAML Query filter by date range

Recently I was working on reporting using the JavaScript client object model in SharePoint 2013. Here my requirement is to retrieve records from a SharePoint 2013 list between created date. Let’s say I want to retrieve records created between 1st Jan to 30th Apr 2015.

So I have written the query in the below format where startDate is the created start date and endDate is the created end date. And also I have included IncludeTimeValue=’FALSE’, so that it will not consider the time part in the created date.

var startDate="2015-01-01T00:00:01Z";
var endDate="2015-30-04T00:00:01Z";

query = "<View Scope='RecursiveAll'><Query><Where><And><Geq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>"+startDate+ "</Value></Geq><Leq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>"+ endDate + "</Value></Leq></And></Where></Query></View>";

When I run this query, it did not give me the result. After doing some research, I found that I gave the incorrect date format.

I had given the date format as “yyyy-dd-mmT00:00:01Z“. And it was the reason the filter was not working. When I change the format to “yyyy-mm-ddT00:00:01Z” the filter started working. So my startDate and endDate will be like below:

var startDate="2015-01-01T00:00:01Z";
var endDate="2015-04-30T00:00:01Z";

This is a very silly issue, but just want to share because if you ever face this kind of issue, you may check the date format. Will surely save your time.

CAML Query for Boolean field for SharePoint

Let us see, how to use CAML query for boolean field while working with client-side object model (csom) in SharePoint 2013/2016/Online.

We had one SharePoint Online document library where we have added one field as “IsCopied” which is of type Boolean. We wanted to query from the document library based on True/False. So we wrote the query like below:

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Boolean'>TRUE</Value></Eq></And></Where></Query></View> }

But it did not return the result for us. We thought it will return results where IsCopied is TRUE.

Actually, the Boolean field in SharePoint works in 1 (TRUE) and o (FALSE).

So we have to modify the code like below:

SharePoint 2013 caml query boolean field (For True Condition)

First we will see how to use caml query in SharePoint 2013/Online for boolean field for true condition.

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Boolean'>1</Value></Eq></And></Where></Query></View> }

SharePoint 2013 caml query boolean field (For False Condition)

Here we will see how to use caml query in SharePoint 2013/Online for boolean field for false condition.

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Boolean'>0</Value></Eq></And></Where></Query></View> }

Now if you will query it will return the result correctly.

SharePoint 2013 caml query boolean field (Other Approach)

Instead of writting Type=’Boolean’, we can change to Type=’Bool’ then the query will work fine.

var q = new CamlQuery() {<View><Query><Where><And><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + docTitle + "</Value></Eq><Eq><FieldRef Name='IsCopied' /><Value Type='Bool'>True</Value></Eq></And></Where></Query></View> }

Here I have shown how to use SharePoint 2013 CAML query boolean field.

Filter CAML Query by List Item ID in SharePoint Online

This SharePoint CAML tutorial explains, how to retrieve list items from SharePoint Online/2013/2016 based on ID. We will see how to filter the CAML query by list item ID in SharePoint Online or SharePoint 2016/2013.

In SharePoint, the best approach to get the data from SharePoint a list is to query those using either the SharePoint Server Object model or the client object model. Using queries you guarantee that only list items you really need are retrieved from the database and constructed to objects by SharePoint.

Most of the time you don’t need the full collection of list items, so it does not point to ask all the items and then use only a few of these. Let’s see how we can query SharePoint lists using the Client object model.

CAML query to find an item from a SharePoint 2013 List based on the “ID” value that is being retrieved through JavaScript SharePoint web service interface Lists.asmx that provides a set of simple remote methods calls ID Type deprecated in 2013.

SharePoint 2010 (caml query to retrieve sharepoint list item by ID)

Below is the CAML query to retrieve the list item by ID in SharePoint 2010/2013.

<Query><Where><Eq><FieldRef Name="ID" /><Value Type="Counter ">' + ID + '</Value></Eq></Where></Query>

SharePoint 2013/Online (caml query to retrieve sharepoint list item)

Below is the CAML query to retrieve list items by ID in SharePoint 2013/Online.

<Query><Where><Eq><FieldRef Name="ID" LookupId="TRUE"/><Value Type="Text">' + ID + '</Value></Eq></Where></Query>

I am using an ajax / Jquery script calling the getlistitems() method to filter the results using a query in SharePoint.

Use the following procedure to create a sample.

Step 1: Navigate to your SharePoint 2013 site.

Step 2: From this page select Site Actions | Edit Page:

Edit the page, go to the “Insert” tab in the Ribbon and click the “Web Part” option. In the “Web Parts” picker area, go to the “Media and Content” category, select the Script Editor Web Part and press the “Add button”.

Step 3: Once the Web Part is inserted into the page, you will see an “EDIT SNIPPET” link; click it. You can insert the HTML and/or JavaScript as in the following:

Script:

<script src="/Style%20Library/Scripts/jquery-1.10.1.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function () {
findtext(8);
});
function findtext(id) {
var soapEnv =
"<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
<soapenv:Body> \
<GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
<listName>companyInfo</listName> \
<query><Query><Where><Eq><FieldRef Name='ID' LookupId='TRUE'/><Value Type='Text'>"+id+"</Value></Eq></Where></Query></query>\
<viewFields> \
<ViewFields> \
<FieldRef Name='Company' /> \
</ViewFields> \
</viewFields> \
</GetListItems> \
</soapenv:Body> \
</soapenv:Envelope>";
$.ajax({
url: " /_vti_bin/Lists.asmx",
type: "POST",
dataType: "xml",
data: soapEnv,
complete: processResult,
contentType: "text/xml; charset=\"utf-8\""
});
}

function processResult(xData, status) {
$(xData.responseXML).find("z\\:row").each(function () {
var companyName = $(this).attr("ows_Company");
$('#ResultDiv').html(companyName);
});
}
</script>

Final O/p:
Amazon.com

This is how we can get List items from SharePoint 2013 based on the “ID” column value using CAML Query.

Get Tasks assigned to a user or current user groups in SharePoint using CAML query

This CAML SharePoint tutorial explains how to get tasks assigned to a user or current user groups in SharePoint using CAML query.

Follow the below steps:

Open your site in SharePoint designer 2010/2013. Go to List and Libraries option. Select your Task List to see in fig.

Open the task list in advance mode or .aspx extension file. Find the view tag under this <Query> option. Change by the following code only under the <Query> tag

<View Name=……
<Query>
<Where>
<Or>
<Membership Type="CurrentUserGroups">
<FieldRef Name="AssignedTo" />
</Membership>
<Eq>
<FieldRef Name="AssignedTo" />
<Value Type="Integer">
<UserID Type="Integer"/>
</Value>
</Eq>
</Or>
</Where>
</Query>

Save and check out the page.

This is how we can get task assigned to a user or current user groups in SharePoint using CAML query in SharePoint 2010. The same CAML query we can use in SharePoint 2013/2016 or SharePoint Online also.

SharePoint CAML Query Order by Example

Let us see how to use SharePoint CAML Query Order by in SharePoint 2013/2016/Online. In this example, we will use CAML Query with Order by a condition for a dropdown column in SharePoint 2013.

When I was working with one SharePoint 2013 list having a dropdown column with values from 1 to 13.

I want to filter data order by the dropdown column. But when we use dropdown column directly in caml query it will not filter in the same order as it will consider 1, 10, 11, 12, 13, 2,3,4.. in this order.

For such type of requirement just create one more calculated column as shown below:

Then add this column and use this column in order by instead of dropdown column. This will solve the problem.

CAML Query Helper in SharePoint – Download, Install and Use

Now, let us see how to download and install SharePoint CAML Query Helper. Also, we will see how to use SharePoint CAML Query Helper in SharePoint Online/2013/2010/2007. I will discuss how to connect to the SharePoint Online site from caml query helper.

SharePoint CAML helper tool will help build and test SharePoint CAML queries in SharePoint 2013 On-premise as well as SharePoint 2013 Online Office 365 environment. This tool is very much compatible with SharePoint 2010 and MOSS 2007.

Another feature of SharePoint CAML query helper tool is that you can export fields details and query results in CSV.

Download and Install SharePoint CAML Query Helper

Let us first and download and install SharePoint CAML query helper in the laptop.

• Download SharePoint CAML query helper from GitHub.

Once your download and unzip you will see two folders. In one folder as the name suggest a setup for MOSS 2007 and SP2010. And another folder contains the ext for SP2013. Open the SP2013 folder and double click on SPCAMLQueryHelper2013.exe.

This will open the dialog box and ask you how to connect to the SharePoint site. You can connect by using the SharePoint object model (in this case tool needs to be installed in the server where SharePoint has been installed). Also, you can connect by using a web service. And also you can connect to your Office 365 SharePoint online site by using web services. First, we will see how we can connect to an on-premise site.

Connect to SharePoint On-Premise site from CAML query helper

Now, we will first connect to SharePoint 2013 site from CAML query helper. Select Use SharePoint Object Model and click on SUBMIT.

Then it will ask you to give the Site URL, enter the URL and click on Load. This will load all your List and SharePoint Document libraries. Then you can double-click on a particular list and it will load the columns details in the List Info tab, you can also get more details about the list using the MORE LIST DETAIL button as shown in the fig below:

Connect to SharePoint Online Site from CAML Query Helper

Reload the tool again, And this time Choose option “Use Web Services (Office 365)” and then enter the Username and Password which you connect to SharePoint Online site like below. Because here we will connect to a SharePoint Online Office 365 from SharePoint CAML helper.

Then in the Next screen give the Site URL and click on LOAD. It will Load all the lists and Libraries from the SharePoint Online site.

Now double click on the particular list for which you want to write the query. This will load the list information in the List Info tab. You can able to see more details about the list by clicking on the “MORE LIST DETAIL” button. Apart from this, you can also export all the List Information by clicking on the EXPORT button.

Build a CAML Query using SharePoint CAML Query Helper

To write the query, click on the Query Helper tool. This provides three tabs Query, View Fields and View Attributes.

From the Fields box, you can drag and drop to the Query text box. In the Row Limit box, enter the value (Row limit). Click on the Search button to search the query.

Then copy the code by clicking on the Copy Code button which will generate the CSOM code which will be look like below:

string sQuery = @”<Query><OrderBy><FieldRef Name=””Modified”” Ascending=””FALSE””></FieldRef></OrderBy><Where> <BeginsWith><FieldRef Name=””Title””></FieldRef><Value Type=””Text””>item</Value></BeginsWith></Where></Query>”;
string sViewFields = @”<FieldRef Name=””Title”” /><FieldRef Name=””First_x0020_Name”” />”;
string sViewAttrs = @”Scope=””Recursive”””;
uint iRowLimit = 100;

var oQuery = new SPQuery();
oQuery.Query = sQuery;
oQuery.ViewFields = sViewFields;
oQuery.ViewAttributes = sViewAttrs;
oQuery.RowLimit = iRowLimit;

SPListItemCollection collListItems = oList.GetItems(oQuery);

foreach (SPListItem oListItem in collListItems)
{
}

This is how we can download, install and use CAML Query helper for SharePoint Online/2013/2016.

You may like following SharePoint tutorials

• SharePoint rest api create folder
• Save site as template in SharePoint 2013/2016/Online
• How to create custom webpart in SharePoint 2013 step by step
• Get sharepoint site template PowerShell
• SharePoint list templates | SharePoint list template id
• How to get sharepoint site URL programmatically (CSOM and JSOM)
• Enable Chart Web Part in SharePoint 2013/2016/Online
• How to create a calculated column in SharePoint List or Library

Conclusion

In this caml query builder online tutorial, I have explained how to download and install U2U CAML Query Builder for SharePoint. How we can use CAML query builder in SharePoint 2013/2016/Online.

Also, we checked how to use calm query builder to query SharePoint list. This is how we can use the SharePoint CAML Query builder.

We covered:

• CAML Query Builder in SharePoint Online or SharePoint 2013, SharePoint 2016 or SharePoint 2019
• Advantages of CAML query builder tool to generate CAML query
• Download & Install CAML Query Builder in SharePoint
• How to use CAML query builder in SharePoint
• SharePoint CAML query example
• CAML Query for Boolean field for SharePoint
• SharePoint CAML Query filter by date range
• Filter CAML Query by List Item ID in SharePoint Online
• CAML query to filter SharePoint list items
• Get Tasks assigned to a user or current user groups in SharePoint using CAML query
• SharePoint CAML Query Order by Example