0

I've been struggling with a CAML query that will allow me to retrieve all items from a SharePoint Online document library and getting all the metadata as well. I managed to get some, but not all columns, so my query is not properly structured, I guess. I retrieve items with ID from 0 to 100, and I only get 10 of them, just because I want to fix the metadata retrieval first and then deal with everything else. Also, I only want to get files and not folders. I only get certain metadata fields/columns, but definitely not all. For example, I want to get the contents of a field/column with internal name Description0.

Here is a sample of my code:

 private static void ExecuteListUpload()
    {
        using (var context = new ClientContext("domain.sharepoint.com/sites/siteName/subsiteName/"))
        {
            var password = new SecureString();
            var plainPassword = "Password";

            foreach (var @char in plainPassword) password.AppendChar(@char);
            context.Credentials = new SharePointOnlineCredentials("user@domain.com", password);

            List list = context.Web.Lists.GetByTitle("Document Library");

            context.Load(list);
            context.ExecuteQuery();

            CamlQuery query = new CamlQuery() ;
            query.ViewXml = @"<View Scope='RecursiveAll'>
                                <Query>
                                <FieldRef Name='Description0' />
                                    <Where>
                                        <And>
                                            <And>
                                                <Gt>
                                                    <FieldRef Name='ID'></FieldRef>
                                                    <Value Type='Number'>0</Value>
                                                </Gt>
                                                <Lt>
                                                    <FieldRef Name='ID'></FieldRef>
                                                    <Value Type='Number'>100</Value>
                                                </Lt>
                                            </And>
                                            <Eq>
                                                <FieldRef Name='FSObjType' />
                                                <Value Type='Integer'>0</Value>
                                            </Eq>
                                        </And>
                                    </Where>
                                    <OrderBy>
                                        <FieldRef Name='ID' Ascending='True' />
                                    </OrderBy>
                                </Query>
                                <FieldRef Name='Description0' />
                                <RowLimit>10</RowLimit>
                                <ViewFields /><QueryOptions />
                              </View>";


            ListItemCollection items = list.GetItems(query);
            context.Load(items);
            context.ExecuteQuery();

            Console.WriteLine(items.Count);

            foreach (ListItem item in items)
            {
                string fileInfo = String.Format(item.FieldValues["FileLeafRef"] 
                // would like to retrieve all fields here
                    );
                Console.WriteLine(fileInfo);
            }
        }

I also tried the following (from here https://social.technet.microsoft.com/Forums/en-US/9fb66816-0fbf-4dff-a4ca-eb962ee61084/csom-caml-query-to-get-all-listitems-with-their-fieldvalues?forum=sharepointdevelopment):

ClientContext ctx = new ClientContext("http://sp2013/sites/team");

List oList = ctx.Web.Lists.GetByTitle("CustomList45");

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View Scope=\"RecursiveAll\"></View>";

ListItemCollection collListItem = oList.GetItems(camlQuery);

ctx.Load(collListItem, items => items.Include(
 item => item.Id,
 item => item.DisplayName,
 item => item.HasUniqueRoleAssignments,
 item => item.FieldValuesAsHtml,
 item => item.RoleAssignments));

ctx.ExecuteQuery();

But I get Microsoft.SharePoint.Client.ServerException: 'Value does not fall within the expected range.' If I remove

item.DisplayName

from the query, it works fine, but I still don't get all the metadata fields.

0

It turns out that the solution is bit of both code snippets, I shared, combined with proper syntax in the CAML query. In the end of the CAML query I have to include the ViewFields tag:

So this:

 <ViewFields />

became this:

<ViewFields>
   <FieldRef Name='Description0' />
</ViewFields>

I was confused at first, because I was left to believe, when reading some articles, that if you leave the ViewFields tag empty, you will get all the fields, but that was not the case, at least in my situation. So now I can get all the fields this way - by explicitly specifying them in the query.

Here is the end code - remember, this is just a proof-of-concept kind of thing.

private static void ExecuteListUpload()
    {
        using (var context = new ClientContext("domain.sharepoint.com/sites/siteName/subsiteName/"))
        {
            var password = new SecureString();
            var plainPassword = "Password"; 

            foreach (var @char in plainPassword) password.AppendChar(@char);
            context.Credentials = new SharePointOnlineCredentials("user@domain.com", password);

            List list = context.Web.Lists.GetByTitle("Document Library");

            context.Load(list);
            context.ExecuteQuery();

            CamlQuery query = new CamlQuery() ;

            query.ViewXml = @"<View Scope='RecursiveAll'>
                                <Query>
                                    <Where>
                                        <And>
                                            <And>
                                                <Gt>
                                                    <FieldRef Name='ID'></FieldRef>
                                                    <Value Type='Number'>1</Value>
                                                </Gt>
                                                <Lt>
                                                    <FieldRef Name='ID'></FieldRef>
                                                    <Value Type='Number'>100</Value>
                                                </Lt>
                                            </And>
                                            <Eq>
                                                <FieldRef Name='FSObjType' />
                                                <Value Type='Integer'>0</Value>
                                            </Eq>
                                        </And>
                                    </Where>
                                    <OrderBy>
                                        <FieldRef Name='ID' Ascending='True' />
                                    </OrderBy>
                                </Query>
                                <RowLimit>10</RowLimit>
                                <ViewFields>
                                    <FieldRef Name='Description0' />
                                </ViewFields>
                                <QueryOptions />
                              </View>";

            ListItemCollection itemsCollection = list.GetItems(query);
            context.Load(itemsCollection, items => items.Include(item => item.FieldValuesAsText));
            context.ExecuteQuery();

            Console.WriteLine(itemsCollection.Count);

            var sb = new StringBuilder();


            foreach (ListItem item in itemsCollection)
            {
                sb.Append(item.FieldValuesAsText["FileLeafRef"].ToString());
                sb.Append(" ");
                sb.Append(item.FieldValuesAsText["Description0"].ToString());

                Console.WriteLine(sb.ToString());

                sb.Clear();
            }
            Console.ReadKey();
        }

    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.