CAML queries: how to filter folders from result set?

Question

I'm using caml query to select all documents which were modified or added by user. Query runs recursively on all subsites of specified site collection.

Now problem is I can't get rid of folders which are also part of result set. For now I'm filtering them from result datatable. But I'm wondering: Is it possible to filter out folders from result set just by using caml?

Conception

Answer 1

This CAML actually does the trick:

<Where>
    <Eq>
        <FieldRef Name='FSObjType' />
        <Value Type='Integer'>0</Value>
    </Eq>
</Where>

that will not give you any folders.

Keratinize

I my search for a folder-only query i came to this topic. This CAML query actually works for me on SharePoint 2013. I changed the int to 1 ofcourse, but 0 works great if you want files only. – Thema

This works from CSOM <Where><Eq><FieldRef Name="FSObjType" /><Value Type="Integer">0</Value></Eq></Where> – Wentzel

You MUST use Scope='Recursive' instead of Scope='RecursiveAll' as ViewAttributes to get only files. Scope='RecursiveAll' will always return files AND folders from the library. – Assyria

Answer 2

So, i figured it out :) You can use FieldRef='ContentType' in your caml query and specify typr of content type which you want to select or exclude from select.

So in my case I've added this condition to my caml expression:

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

NOTE: There are problems in multi language setup. Name of content type can be different, so it is good to get names of content types from resources

UPDATE:

It looks like I was too quick in my assumptions. I need to filter out all contett types based on folder content type, because in our projects such content types are used :(

I was not able to create workable query in caml, so I added view field element to my query which selects ContentTypeId of list item and I filter-out rows which are based on folder content type.

Code to do this is trivial, but it bothers me that such simple task cannot be done by pure caml.

Conception

I downvoted your answer because the answer of Johan Leino works on SharePoint 2013. That should be the accepted answer as of now. – Thema

I upvoted this because REST call fails for $filter=FSObjType eq 0 for some strange reason. ContentTypeId works, though looks like a hack. – Inerrant

Some info here: sharepoint.stackexchange.com/questions/124846/… – Inerrant

Answer 3

According to the output from CAML Designer (created by Karine Bosch and Andy Van Steenbergen and distributed by the Belux Information Worker User Group) the correct CAML syntax is:

<Where>
  <Eq>
    <FieldRef Name='FSObjType' />
    <Value Type='Integer'>0</Value>
  </Eq>
</Where>
<QueryOptions>
  <ViewAttributes Scope='RecursiveAll' />
</QueryOptions>

I tested this in PowerShell and got the expected result:

$qry = new-object Microsoft.SharePoint.SPQuery
$qry.Query = "<Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></Where><QueryOptions><ViewAttributes Scope='RecursiveAll' /></QueryOptions>"

$items = $lib.GetItems($qry)
$items.Count

You can replace the <QueryOptions> tag in the CAML with the SPQuery object property ViewAttributes if you prefer, ie $qry.ViewAttributes = “Scope=’RecursiveAll’".

When testing this be sure to re-instantiate the SPQuery object each time as you cannot simple reassign the Query property. Once the query has been executed, any new value assigned to the Query property is ignored. So execute the entire PowerShell fragment.

Indonesian

Answer 4

If you're working with folders and you're using an SPQuery object you also may want to use the ViewAttributes field to allow for recursive item retrieval. Setting the value to Scope="Recursive" will retrieve items from subfolders and will not retrieve the folder objects in the result set.

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

Hammer

Answer 5

Dave T.'s answer didn't work for me but taking it as an inspiration here's what I came up with:

<Where>
  <BeginsWith><FieldRef Name="ContentTypeId" />
    <Value Type="ContentTypeId">0x0101</Value>
  </BeginsWith>
</Where>

The main problem is that we can only know ContentTypeId on a site level, because when a content type gets added to a list/library it becomes a list content type and its ID is appended with another GUID (0x010100...). And there's no NotBeginsWith condition in CAML, so we can not filter out folders, but we can include documents only which site content type id is 0x0101.

The solutions with FSObjType field don't work for me because I have libraries with far more files than a threshold so all the fields in the query must be indexed and I haven't found a way to index this field.

Ellamaeellan

Answer 6

This is what worked for me

   <Where>
      <Eq>
         <FieldRef Name='FSObjType' />
         <Value Type='Number'>1</Value>
      </Eq>
   </Where>

Project

Answer 7

Check my other answer CAML query that includes folders in result set

Simply changing the operators might get you what you need

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

I'm not sure if that'll work.

Buddhism

Answer 8

For me also it worked as written below:-

<Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where>

Isley

<Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where> – Isley