SharePoint: Content Query Web Part Customizing the Query

Recently I’ve been customizing Content Query Web Parts (CQWP) whereby I override the query and supply my own CAML query. The basic steps involved in doing this are;

1. Author the Web Part using the UI
2. Export the Web Part using the UI
3. Customize the Web Part .webpart file
4. Import the customized Web Part back into SharePoint

Some of these Web Parts were configured to query by Content Type, such that the Web Part property “ContentTypeName” is given a specific value as shown below;

    <property name="ContentTypeName" type="string">Period View Item</property>

Prior to customizing the CQWP query with CAML, such a Web Part worked fine. So having exported the Web Part I provided a custom CAML query using the “QueryOverride” property;

  <property name="QueryOverride" type="string"><![CDATA[<Where>...</Where>]]></property>

After importing the Web Part back into SharePoint I noticed that it would return results for other Content Types also. To work around the problem I modified the CAML query to restrict the results to the Content Type in question, like so;

  <Where><And><And><Eq><FieldRef Name="ContentType" /><Value Type="Choice">Period View Item</Value></Eq>......</And></And></Where>

I suspect this is by design rather than a bug, however the CQWP documentation indicates that the query by Content Type feature is a filter rather than part of the query, as this article aludes to.

New U2U CAML Query Builder Feature

U2U have launched a new version of the CAML Query Builder tool which you can now install as a feature in SharePoint.

Here’s a screen shot…

Querying Document Libraries (or Pulling Teeth) With CAML

This post concerns yet another odd “feature” exhibited when using CAML to query List or Document Library content.

When you create a new Document Library you get a set of default columns, “Name” and “Title” amongst them. By default when you upload a document to a document library the “Name” column metadata is populated with the document filename and the “Title” column is left empty. On the default All Documents view the “Title” column is not displayed.

As shown above I’ve got a document library with folders and files in, what I want to do is query this doc lib (and it’s folders and subfolders) for documents where the document name contains some string value. So consider the following CAML query:

<Query>
   <Where>
      <Contains>
         <FieldRef Name='LinkFilename' />
         <Value Type='Text'>token</Value>
      </Contains>
   </Where>
   <OrderBy><FieldRef Name='FileDirRef' /></OrderBy>
</Query>
<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='LinkFilename' />
   <FieldRef Name='FileDirRef' />
</ViewFields>
<QueryOptions>
   <ViewAttributes Scope='Recursive' />
   <OptimizeFor>FolderUrls</OptimizeFor>
</QueryOptions>

It looks ok, but when you submit this query to SharePoint you get an error message:

“Invalid column name c2″

In fact the c2 part could be any number, but it was c2 in my case. LinkFilename in case you’re wondering is the internal column name of the “Name” column. After much head scratching and teeth gnashing, it turns out that you can’t use the LinkFilename column in a Where clause, you can use it in the OrderBy clause just fine, for no apparent reason I can fathom.

Ok so if you can’t use LinkFilename in a Where clause, you can alternatively use the FileLeafRef column which contains the document name part of the document URL prefixed with it’s list ID as shown below:

So my final query which gives me the results I want is:

<Query>
   <Where>
      <Contains>
         <FieldRef Name='FileLeafRef' />
         <Value Type='Text'>token</Value>
      </Contains>
   </Where>
   <OrderBy><FieldRef Name='FileDirRef' /></OrderBy>
</Query>
<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='LinkFilename' />
   <FieldRef Name='FileDirRef' />
   <FieldRef Name='FileLeafRef' />
</ViewFields>
<QueryOptions>
   <ViewAttributes Scope='Recursive' />
   <OptimizeFor>FolderUrls</OptimizeFor>
</QueryOptions>

This gives me all the documents within a document library, its folders and subfolders, where the document name contains a specific string. The cool part of that statement (querying across folders and subfolders) is accomplished by setting the Scope option of the ViewAttributes property of the SPQuery class. The Scope option relates to the Scope property of the SPView class and it’s permissable values are:

	Member name	Description
	Default	Show only the files and subfolders of a specific folder.
	FilesOnly	Show only the files of a specific folder.
	Recursive	Show all files of all folders.
	RecursiveAll	Show all files and all subfolders of all folders.

 

As  you may have noticed I was using U2U’s excellent CAML Query Builder to create and submit CAML queries.

Deleting Items from Sharepoint Document Libraries

Deleting items from Sharepoint Document Libraries or Lists should be fairly straightforward, however there are a couple of gotcha’s depending on which method from Sharepoint Webservices you want to use and accordingly, there are differences in the formatted CAML command you would use.

Firstly a Document Library is a form of List, but the CAML command to delete items from these differ slightly.

To delete an item from a list you would format a CAML command like this;

<Batch OnError="Continue">
  <Method ID="1" Cmd="Delete">
    <Field Name="ID">1</Field>
  </Method>
</Batch>

The ID field contains the ID value of the list item to delete.

To delete an item from a document library you would format a CAML command like this;

<Batch OnError="Continue">
  <Method ID="1" Cmd="Delete">
    <Field Name="ID">1</Field>
    <Field Name="FileRef">http://spdev/Library Name/Msg-4218faa2-727e.xml</Field>
  </Method>
</Batch>

Of note here is the ID and FileRef fields. The ID field is required to be present but it’s value is ignored. The FileRef field contains the document items FileRef column value and this is used to ascertain the document item to delete. This value is the full URL of the document item to delete, however it should be noted that this URL must not be URL encoded.

In both cases it is important that there is no whitespace/linefeed characters in the Batch elements inner or outer XML, although the examples shown above contain LF characters for clarity.

Both the above commands would be executed using the Sharepoint Webservices UpdateListItems() method, the first parameter of which is the List/Document Library GUID and the second is the XML node containing the CAML Batch command.

The List/Document Library GUID should be formatted using braces and hyphen characters as show below.

{508B0C42-BC06-42A2-9531-2844FEAE9C00}