Collaborative Markup Language (CAML) Query Builder in Sharepoint 2010

Jean Paul

SharePoint 2010CAMLCAML QueryCAML Query Builder

8 Comments

Join the conversation! Your thoughts help the community grow.

Jean Paul14y ago
Hello Mahesh, You can use the SPQuery class to use the CAML query text you have generated. A link is given below: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.aspx Regards, Jean Paul
Mahesh Babu14y ago
Hi Paul, I got result in CAML Query Editor. but how to use this result in our sharepoint application. Please tell me how to proceed in this scenario...
Jean Paul14y ago
No Problem Dude!
Mahesh Babu14y ago
ho!! thanks for clarification...
Jean Paul14y ago
Direct SQL is not recommended in SharePoint. Microsoft owns the content database and the db design / procs may change as each version move through. The recommended ways of interaction is Server Object Model. So we have to depend on the Server Object Model possibilities like CAML, LINQ.
Mahesh Babu14y ago
We have SqlServer to do all queries, then why should we do this CAML Query builder again...Could you please explain?
Jean Paul14y ago
Not really Gaurav! Functions are not supported in CAML.
Gaurav Gupta14y ago
Can we create functions using CAML?

In this article we can explore the SharePoint 2010 free tool CAML Query Builder.

What is CAML?

The Collaborative Markup Language (CAML) is an XML based language for querying and updating SharePoint objects.

What are the Advantages of this Tool?

We can see the following are the advantages of using this tool:

Since CAML is non-typed and error-prone during development the CAML Query Builder provides the advantages of writing and validating the CAML queries
An intuitive User Interface is provided by the application for easier Query building
The tool is free

Download

You can download the tool from following location:

http://www.u2u.be/res/tools/camlquerybuilder.aspx

Please make sure you download the 2007 version which is working well with SharePoint 2010.

Download the tool and extract the zip file to a temporary folder. Execute the setup.exe inside it and follow the wizard to install the tool.

Note: The SharePoint binaries are required for working with this tool.

Starting with the Tool

After the installation execute the tool from the Start Menu or Desktop. The tool is available on Start Menu > U2U > CAML Builder > CAML Builder as shown below:

On execution you will see the following screen:

Enter the following details as shown above:

URL of the site
Connect through SharePoint Web Services
User Credentials

After entering the details click the Connect button.

Note: Since the tool is for SharePoint 2007, the Connect via Object Model option cannot be used.

Inside the Tool

On connecting successfully you will get the following screen:

You will see that the preceding screen displays your existing Lists and Libraries. Additionally it displays the Lists & Libraries which are hidden in the Quick Launch too.

Create a Contacts List

To start, create a Contacts list of Contacts template inside your SharePoint site. Insert the following test data into it and refresh the CAML Query Builder Tool.

Building your first CAML Query

Now we can try building our first query using the CAML Query Builder Tool.

Here we are trying to find the list items for which the First Name contains "1".

Step 1: Start the New Query pane

Right-click on the Contacts list from the left side pane and choose the New Query menu.

You can also use the New Query button from the tool bar.

Step 2: Specify the Column Parameters

From the pane on the right side, choose the Filter Column > First Name using the second drop down list as shown below.:

Now choose the option Contains from the third drop down list. Enter the text "1" in the textbox.

Note: The following are the drop down lists shown above and the purpose:

The first drop down list contains the options Filter, And, Or.
The second drop down list contains the Column Names of the selected list.
The third drop down list contains the conditions like Equal, Not Equal, Contains etc.

Step 3: View the CAML Query

On performing the preceding steps you will see that the CAML Query is generated for the preceding condition. You will see the CAML Query in the bottom pane as shown below.

Step 4: Executing the CAML Query

Now you are ready with the CAML Query but it is not yet tested. To test the query use the Execute query button from the top toolbar as shown below:

You should be getting the results as shown above.

So now you are ready with the CAML Query which is tested and can be copied to your application.

Step 5: Adding OR Condition

Now we can play a little bit more by adding an OR Condition to list all the Contacts items which contains "1" or "2" in the First Name column.

To do that select the Or option from the Filter drop down list.

You will see that a new filter row is added to the query pane. Enter the new parameters in the new row as shown below.

Step 6: Execute the New Query

Now you can try executing the preceding query and this time the result should show both the records from the Contacts list.

Add Order By Element

Now we can try using the Add Order By element button. This option allows us to add the Order By CAML tags. You can specify a column and the order (ascending or descending).

Create a new Query and click on the Add Order by element button from the toolbar.

Choose the ID column and order as Descending.

On executing the query you will see the following result:

Please note that the items are now shown in the Descending order of ID values.

Multiple Order By Columns

We can have multiple Order By columns. Use the same button again to add a new Order By CAML tag as shown below.

This time we are having 2 Order by columns. The Created By column with Ascending sorting.

More Options in CAML Query Builder Tool

You can explore more options in building CAML Queries using the tool like:

Creating Queries for Date Columns
Trying with And operator
Filtering with Equal To, Lower Than, Greater Than conditions
Using the Copy Query button to copy the query to clipboard

References

http://tinyurl.com/sp2010-camlqb

Summary

In this article we have explored the tool CAML Query Builder. In real-life scenarios this tool should give the time, cost & efficiency advantages.