(Migrated post from June 19th, 2007; Updated on July 12th (see comments))

Facing the challenge to write a custom query that spans multiple lists that contain subfolders and a rather complex set of metadata, we had to evaluate both methods of querying lists:

• SPQuery
• SPSiteDataQuery

Looking at the SPQuery, the implementation is a piece of cake. However, there is a known limitation that SPQuery will return only results of one folder, you’ll never get search results from any of the subfolders contained in the library:

(from the WSS 3.0 SDK):
SPQuery can only get the items in the current folder; SPQuery will not get the items in subfolders. This is the design feature in SharePoint Portal Server 2003. To get the items in subfolders, it is necessary to change to the subfolder in SPQuery first. Also it is necessary to specify the format for using dates in the query.

OK, so far so good. But let’s look now at SPSiteDataQuery which is new in WSS 3.0 – and you’ll notice!

First of all, make sure that you really read and understand (and least try!) the documentation at http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx.

Only if you stay 100% within the given specification, you’ll be able to obtain results, otherwise, you’ll always get zero search results.

Here are some of my findings:

• You must write the CAML query without the “<Query>” Element.
• If writing the CAML Query as a string, you must not use any line breaks or additional whitespaces.
  • Example of a working query:
    "<Where><Eq><FieldRef Name='TestLookupMulti' /><Value Type='Text'>abc</Value></Eq></Where><OrderBy><FieldRef Ascending='False' Name='Title' /></OrderBy>"
  • Example of an “invalid” query (sorry, I've seen the comment, but this one did not work for me!):
    @“<Where>
                           <Contains>
                                                   <FieldRef Name='TestLookupMulti' />
                                                   <Value Type='MultiLookup'>SubFolder</Value>
                           </Contains>
     </Where>
    <OrderBy>
                           <FieldRef Ascending='False' Name='Title' />
    </OrderBy>"
    
    
• In order to query for individual metadata fields (fields that not present in every list like “Title” or “ID”) you must include the “Nullable=’true’” Attribute in the <fieldref>-Element of the ViewFields. However: This does not work for lookups and person-fields! If you are using custom lookups, you must restrict the lists that are searched using the <Lists> Element. Here is a working example:
  spDataQuery.Lists = "<Lists><List ID='" + myTestListID + "' /></Lists>";
• You may use Lookup and even Multivalue-Lookups for your queries. Even the <Eq> Operator works on Multivalue-Lookups! However, up to my knowledge, it is not possible to show a Multivalue-Lookup in the ViewFields! Every attempt to do this will result in zero search results!

I will now provide a working example that uses bot: SPQuery and SPSiteDataQuery.
Create a PictureLibrary called “MyPictures” with some subfolders. Add the following metadata:

• “Test” à Simple Text
• “TestLookupSingle” à Single-Select Lookup
• “TestLookupMulti” à Multi-Select Lookup

Use the code below in a test windows application (running on the server!) and make sure you have the necessary privileges to execute the query on MOSS: