CAML Query Builder for SharePoint 2003 and SharePoint 2007


	Building a CAML Query for the SharePoint Lists Web Service
	Supported Methods The GetListItems method The UpdateListItems method The GetListItemChanges method The GetListItemChangesSinceToken method

	Supported Methods
	The U2U CAML Query Builder can create CAML that you can use against methods of the Lists.asmx web service, which is one of the SharePoint web services. Methods supported by this tool: GetListItems UpdateListItems GetListItemChanges GetListItemChangesSinceToken

	The GetListItems method
	This method returns list items from a specific list depending on the criteria you pass. You can get your list items sorted, you can pass filter criteria and you can specify additional query options. You can find more information about this method and its arguements in the MSDN library. To start building a query for this method, you have to connect to your SharePoint site choosing the Connect via SharePoint Web Services option. The left treeview is populated with the lists and document libraries from the connected SharePoint site. Right-click the list of for which you want to build the necessary CAML and choose the GetListItems menu item. The right upper part of the form, where you can build your query, shows 3 tabs: View Fields Query Query Options

	Getting the fields you only need
	In the View Fields tab you can choose the columns you want to have returned in the result set. The left list box contains all columns of the selected list. Select a field and click on the > button to add the field to the right list box. You can also add all columns, remove a columns from the selected fields or remove them all. The <ViewFields> node for the GetListItems method is build dynamically.
	Defining the sort order and filter criteria
	Click on the Query tab. This will present you a user interface for building the query. This is the same UI you get when building a query for the SPQuery object. The UI is very intuitive. You can set a sort order on one or more fields and you can also specify a filter to limit the list items returned by the GetListItems method. For a detailed description of this tab, see the Building a CAML Query for the SharePoint Object Model section.

	Setting the query options
	For the GetListItems method you can set some query options: Include mandatory columns: When this option is set, the result set will contain all required columns, together with the columns you selected in the ViewFields tab and some system columns. Date in UTC: When this option is dates in the result set are returned in UTC format. Paging: If you want to do paging, you can specify a server-defined bookmark that causes the method to return the next page of data. Query all folders and sub folders: This option is explained in detail in next section. Query sub folder: This option is explained in detail in next section.

	Folder Support
	Recently I got a question about querying items in sub folders of a document library, for which support is missing in the current versions of the query tool. If you use the object model you typically use an object of type SPQuery. In that case you can set properties of the object to query sub folder: Set the ViewAttributes property to "Scope=\"Recursive\"" to query the root folder and sub folders of a certain document library Set the Folder property to the name of a folder to search through the items in a certain sub folder. Following a post of Mart Muller, you can also use SPSiteDataQuery to query through all folders and sub folders of a document library: http://blogs.tamtam.nl/mart/CommentView,guid,3fca3180-6dd5-40c1-9509-8b785d306e7b.aspx If you need to pass your CAML queries through the web services of SharePoint, you need to include the necessary CAML into your query. As the windows version of the U2U Caml Query Builder supports both working with the object model as working with the web services, I modified the tool so that it can help you building your queries for sub folder support. I will not leave you without any word of explanation. So, for those who are interested, here is how you can build a CAML query with folder support. When you start the windows version of the tool, you have to specify the URL to your SharePoint site. You can specify some credentials if necessary, and you can choose if you want to work through the object model or through the web services of SharePoint. So click the latter. Click the Connect button. In the left pane a treeview will be populated with the lists and document libraries of the SharePoint site of your choice. You can build a good old query by selecting a list, but you can also build a query for use with web services, meaning you can get the necessary xml for the Query node, the ViewFields node and the QueryOptions node required by the GetListItems method of the Lists web service. If this is the case, right-click the list of your choice and select GetListItems from the context menu. This shows you a tabbed control. In the View Fields tab you can select the fields you want to have included in the result. Click the Query tab to build the query itself. Build your query like you always do: select the fields you want to include in the Order By clause; select the fields and set the criteria for the fields you want to include in the Where clause. Nothing new until now. The document library I use in this example has the following structure: 1 document in the root folder with the name "SharePoint developers course.docx" 1 document in the folder "SharePoint For Power Users" with the name "Chapter 1.rtf" 1 document in the folder "SharePoint For Developers" with the name "Chapter 2.doc" So I build a query to find my file starting with the name "c" (which is located in the sub folder). To set some extra query options, click the Query Options tab and now pay attention! If you leave all settings like in the screen shot, and you press the Test button, you will get no result back. This is because the default is that only the root folder is queried. If you want to query a certain sub folder, you can check the "Query sub folder" check box. The tool fills out the name of the document library because this is the root of the path, and you can add the name of a sub folder. You see in the screen shot that my document is returned in the result. The query generated by the tool will look like the following: If you want to look through all folders and sub folders, click the "Query all folders and sub folders" check box. This will include the <ViewAttributes> element into the <QueryOptions> element. When the query executes against my document library, the 2 documents in the 2 sub folders start with "c" and are returned as result. The CAML generated by the tool looks as follows:

	Using the CAML in your code
	The syntax of the GetListItems method is as follows: XmlNode nodeListItems = listService.GetListItems("List_Name", null, nodeQuery, nodeViewFields, null, nodeQueryOptions, null); The only required argument is the list name. All other arguments can be empty or null. In your code you can load the generated xml into an xml document and use it as follows:

	The UpdateListItems method
	This method ads, deletes or updates list items from a specific list based on a Batch query. You can find more information about this method and its arguements in the MSDN library. When connected to your SharePoint site in Web Services mode, you can update list items with the CAML Builder. Right-click the list you want to modify and choose the menu option UpdateListItems. A control with 2 tab pages will be displayed. On the first tab of this control you can decide what you want to do when an error occurs during updating, on which list version you want the updates to happen or on which view. On the second tab you can build your items to Update. Click the Add button to add your first update method. In the Select list box you can choose if you want to Update an item, add a new item or delete an existing item. In this example we will update an item and create a new item. After you have selected the Update method, you have to add the fields you want to modify: select a field in the fields list box fill out a value in the text box click on the ">" button to add the field to the method node in the left treeview Don't forget to add the ID field for the item(s) you want to update. With each step, you will see your CAML change in the CAML Editor pane. If you want to modify a value of a field added to one of your method nodes in the left treeview, click on the field node, modify its value and click on the ">" button again. If you want to modify the command of one of your methods, click on the method node in the left treeview and change the selection in the Command list box. You can even remove a field or a method by clicking the node in the left treeview and then clicking the Remove button. You can test your CAML by clicking the "Test" button in the CAML Editor pane. If no error occurred during the execution of your update, you'll see an error code 0 in the ErrorCode column. In that case you can click the "+" button in the grid and click on the link "Result row" to see your update. If an error occurred during execution, you'll see the error code together with the error message. If you want to create or update an item in a folder of the list you can use the RootFolder attribute of the Batch node. Specify the relative path of the folder as follows: Note that the path for a document library is a little different. See my post on this matter.

	The GetListItemChanges method

	This method retrieves all list items that have been changed since a certain date. If no date is specified, all list items are returned. This method can be used when you need to synchronize list data. You can find more information about this method and its arguements in the MSDN library. When connected to your SharePoint site in Web Services mode, you can update list items with the CAML Builder. Right-click the list you want to modify and choose the menu option GetListItemChanges. The Query control with 3 activated tabs will be displayed: ViewFields, Contains and Since. In the ViewFields tab you can select the columns you want to be returned in the result set. You select these columns the same way you did for the GetListItems method. In the Contains tab you can build a filter-like query: select a field for which you want to define a filter. Enter or select a value to which the returned rows should match and click the > button. In the screen shot all items with a completed status will be returned. You can execute the GetListItemChanges method without a Contains clause and without a date. But if you want to limit the returned rows to those modified after a certain date, you have to define a Since parameter. Click the Since tab, check the Since checkbox and enter a date. If needed you can also specify a time.

	The GetListItemChangesSinceToken method
	Sending a GetListItemChangesSinceToken request without including a token returns the list schema, the full list contents and a token. The token represents the moment when those changes were requested. By including this token the next time you call GetListItemChangesSinceToken, the server returns only those changes that have occurred since the token was generated. This method can be used when you need to synchronize list data. You can find more information about this method and its arguements in following articles: MSDN library. Microsoft SharePoint Developer Documentation Team Blog. When connected to your SharePoint site in Web Services mode, you can update list items with the CAML Builder. Right-click the list you want to modify and choose the menu option GetListItemChanges. The Query control with 5 activated tabs will be displayed: ViewFields, Query, Contains, QueryOptions and ChangeToken. In the ViewFields tab you can select the columns you want to be returned in the result set. You select these columns the same way you did for the GetListItems method. For this method you can include a Contains part or a Query part (as in GetListItems) but you can't have both: the one excludes the other. In the Contains tab you can build a filter-like query: select a field for which you want to define a filter. If this is the first time you run this method, you don't need to specify a token. The token will be returned by the server together with the resulting rows: As of then each execution of the method should contain the returned token to get only the changed rows back: You can also specify a number of QueryOptions. For the details I refer to the documentation mentioned in the beginning of this section.

	Continue with Using the CAML Query Builder DLLs in your code.