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.

stddocview

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:

camlresultview

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.

About these ads

About Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

9 Responses to “Querying Document Libraries (or Pulling Teeth) With CAML”

  1. Hey, I experienced the same thing. LinkFileRef and LinkFilename don’t work well in CAML queries, at least with the code I am using. It works fine with the “Title” field, which is what should be used. LinkFilename is Calculated.

  2. nlvraghavendra November 8, 2010 at 05:01

    Hello

    How can I get the URL of the documents using CAML query?

  3. You could try the EncodedAbsUrl column (see here)

  4. Experienced a load of teeth pulling also. Thumbs up for your article, thanks.

  5. >>LinkFilename
    Thank You!

  6. Have you tried, or had any luck, returning FileDirRef, FileLeafRef, or any other of the names via the ProjectedFields of a join?
    “”

  7. Sorry I haven’t tried that

Trackbacks/Pingbacks

  1. SUPERNOVA | MetroStar Systems' Blog - October 3, 2011

    […] http://platinumdogs.wordpress.com/2009/07/21/querying-document-libraries-or-pulling-teeth-with-caml/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 613 other followers

%d bloggers like this: