|
|
|
|
Building a CAML Query for the SharePoint Lists Web Service
|
|
|
|
|
|
|
|
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:
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.
|