CAML query that includes folders in result set

Question

I'm trying to write a CAML query that executes against a specific SPList, scoped to a specific folder, recursive from that point, and returns all ListItems (which meet a criteria) and Folders.

Here's the code for the query which seems like it should work (formatted for readability):

SPQuery query = new SPQuery();
query.Query = "
<Where>
    <Or>
        <Contains>
            <FieldRef Name=\"FileRef\" />
            <Value Type=\"Text\">foo</Value>
        </Contains>
        <Eq>
            <FieldRef Name=\"FSObjType\" />
            <Value Type=\"Lookup\">1</Value>
        </Eq>
    </Or>
</Where>";

query.ViewFields = "
<FieldRef Name=\"CustomField1\" Nullable=\"TRUE\" />
<FieldRef Name=\"CustomField2\" Nullable=\"TRUE\" />
<FieldRef Name=\"CustomField3\" Nullable=\"TRUE\" />
";

query.RowLimit = 500;
query.ViewAttributes = "Scope=\"RecursiveAll\"";
query.Folder = startingFolder;
DataTable dt = myList.GetItems(query).GetDataTable();

So - this only returns the ListItems - no folders.

If I remove the other conditions from the query, only leaving the FSObjType=1, I get a COM exception "Cannot complete this action. Please try again."

If I then remove the ViewFields, leaving only the Scope=RecursiveAll and FSObjType=1, I get an empty result set back.

Xiphoid

Did you ever solve this? I have the same problem, and unless there's a solution, I will need to go ahead and actually write a recursive function to do it. – Began

@codeflunky unfortunately no, I never got this to work correctly. – Xiphoid

@codeflunky if you do find a solution, please post it here! – Xiphoid

Answer 1

Everyone is close, but not quite right.

using (SPSite site = new SPSite("http://server/site"))
{
  SPWeb web = site.RootWeb; // See disposal guidance http://blogs.msdn.com/b/rogerla/archive/2008/10/04/updated-spsite-rootweb-dispose-guidance.aspx

  SPQuery query = new SPQuery();
  query.Query = @"
          <Where>
            <BeginsWith>
              <FieldRef Name='ContentTypeId' />
              <Value Type='ContentTypeId'>0x0120</Value>
            </BeginsWith>
          </Where>";
  query.ViewAttributes = "Scope='RecursiveAll'";
  SPList list = web.Lists[listId];
  SPListItemCollection items = list.GetItems(query);
  // Do stuff with your folders
}

First of all, using this FieldRef is wrong:

<FieldRef Name='ContentType' /><Value Type='Text'>Folder</Value>

because the folder content type can be inherited. Therefore, you need to compare against the content type ID, like this:

<Where>
  <BeginsWith>
    <FieldRef Name='ContentTypeId' />
    <Value Type='ContentTypeId'>0x0120</Value>
  </BeginsWith>
</Where>

And then, set the view attribute Scope to RecursiveAll

<View Scope='RecursiveAll'>...</View>

That should return any item whose content type inherits from Folder (0x0120)

Iloilo

Answer 2

I don't have my dev image to test against, so I might need to revise this later; but I think you could try

query.ViewAttributes = "Scope=\"Recursive\""; 

Retrieving the items will allow you to use SPUtility.GetUrlDirectory(url) to get the folder path for a given item, and parse the folder hierarchy from there.

Endosteum

"Recursive" searches folders deeply but does not include folder objects in the result set. I would like to get all the information I need in one round trip - making dozens of API calls on top of the query is less than ideal. – Xiphoid

If I may ask, are you using this as a provider for some UI element such as a tree view that can use lazy loading? Or will you need to pull everything on a single pass? – Endosteum

@Gurdas everything needs to be fetched at once – Xiphoid

Answer 3

You could try basing your caml query on the Folder Content Type instead,

<FieldRef Name='ContentType' /><Value Type='Text'>Folder</Value>

whilst keeping the

Query.ViewAttributes = "Scope=\"RecursiveAll\""; 

Bolin

This worked for me. I was able to query by folder content type for folders nested within folders using RecursiveAll. – Denomination

Answer 4

I've solved this putting:

<QueryOptions>
<IncludeAttachmentUrls>True</IncludeAttachmentUrls>
<Folder/> </QueryOptions>

As query option

I found my question about it on stack overflow:

How can I iterate recursively though a sharepoint list using webservices?

Petrosal

Interesting... I'll give this a try. What is the empty folder tag supposed to accomplish? – Xiphoid

It should returns folder, but I can't remember right now. SPList is a very complicated and obscure webservice, sometimes is magic... :) If it doesn't work, I'll check my docs here. – Petrosal

Answer 5

If I remove the other conditions from the query, only leaving the FSObjType=1, I get a COM exception "Cannot complete this action. Please try again."

Did you remove the <Or> tags when you did this? If not it will not run correctly.

Regardless, that does not solve your problem. Have you tried leaving the query empty? Does it return anything?

I have been working on something similar and ran into an issue as well, perhaps it's somewhat related.

Edmondson

Yes, all of my CAML queries are generated programmatically to avoid syntax errors such as a superfluous <Or>. This is not a syntax problem. – Xiphoid

Do you get a result set if you leave the query empty? – Edmondson

Sorry for the late response. I get a result set of items only if I remove all conditions. – Xiphoid

So you get all items in the specified folder? Also, have you tried adding the 'FileRef' and 'FSObjType' fields to the view section? WSS is poorly documented and quite inconsistent. Oh and make sure the view you are running this on has the folder option set. – Edmondson

Answer 6

This still seems to an issue in SP 2010. Here's workaround code that will work for 2007 or 2010, based on this MSDN Forums post that uses the web services:

private static SPListItem RecurseIntoFolders(SPList list, SPFolder parentFolder, string fileReference)
{
    var query = new SPQuery
    {
        Query = "<Where>" +
                "<Eq><FieldRef Name='FSObjType'/><Value Type='Lookup'>1</Value></Eq>" +
                "</Where>",
        ViewFields = String.Format("<FieldRef Name='{0}' />", FileReferenceInternalFieldName),
        ViewAttributes = "Scope='RecursiveAll'",
        Folder = parentFolder
    };

    var items = list.GetItems(query);
    if (items.Count == 0)
        return null;

    foreach (SPListItem item in items)
    {
        parentFolder = item.Folder;

        // TODO: Any other checking that this is the item we want

        return item;
    }
    return RecurseIntoFolders(list, parentFolder, fileReference);
}

Maori

Answer 7

static string GetParentFolder(SPListItem itemToFind, SPFolder folder)  
    { 
        SPQuery query = new SPQuery(); 
       // query.Query =  "<OrderBy><FieldRef Name='Title'/></OrderBy>";
        query.Query = "<Where><Eq><FieldRef Name=\"ID\"/><Value Type=\"Integer\">"+ itemToFind.ID +"</Value></Eq></Where>";
        query.Folder = folder;
        query.ViewAttributes = "Scope=\"Recursive\"";
        SPListItemCollection items = itemToFind.ParentList.GetItems(query);
        int intpartentFolderID=0 ;
        if (items.Count > 0)
        {
        foreach (SPListItem item in items) 
        {

            SPFile f = item.Web.GetFile(item.Url);

            string test11 = f.ParentFolder.Name;
            intpartentFolderID = f.ParentFolder.Item.ID;

            //string test1 = item.File.ParentFolder.Name;

             return (intpartentFolderID.ToString()); 

         }
        }
        return (intpartentFolderID.ToString());     
    }  

Workroom