CAML: Nested Too Deep

I discovered an interesting error recently while working with Microsoft’s Collaborative Application Markup Language (CAML) that, surprisingly, had received no ink. Partly what surprises me about this is that the error may require you to rewrite large sections of your code if you haven't previously considered this SharePoint limitation. I'll start with some context, but first of all the error is:

“Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries,”

So let's back up and describe what CAML is for everyone who isn’t yet experiencing this problem.

CAML is an XML based query language that is similar to, and serves many of the same purposes as SQL and DDL. However, CAML is used primarily to interact with SharePoint lists. You can retrieve data using something like:

<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>hello</Value></Eq></Where>

Which is equilivant to WHERE Title = ’hello’ in a SQL statement. At this point I’m sure you’re asking why exchange 80 characters for 21? I suppose the answer is related to how CAML is primarily used in web services requests. Regardless, the nested too deeply error occurs when you try to insert large amounts of data into a list.

The insert syntax is actually all batch based, so if you have 100 list items to insert into a list, you build them all into one big CAML statement and then send it across the wire with a call to Lists.UpdateListItems(). Your CAML statement will look something like the following:

<Batch OnError="Continue">
    <Method ID="1" Cmd="New">
        <Field Name="Title">Hello<Field>
        <Field Name="Document">5</Field>
   </Method>
   <Method ID="2" Cmd="New">
        <Field Name="Title" >World</Field>
        <Field Name="Document">5</Field>
   </Method>
</Batch>

The problem comes when you want to insert something like 19,642 list items into a list. SharePoint complains with “Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries,” and suddenly you’re stuck with an enormous CAML statement that you need to break up into smaller batches. How small? Well, for me 300 worked and 500 didn’t, so I set my batch size to 300.

This means that if you are currently experiencing this problem, then have fun rewriting your code to use smaller batches. And, if you're lucky enough to be reading this before you experience the error, then prepare yourself now for this possibility.

Or, if performance isn’t an issue and you don’t want to or can’t rewrite the code that built your CAML then feel free to use this code to batch up CAML batches into smaller batches.

/// <summary>

/// Breaks a larg CAML query into smaller batches to avoid the error "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

/// </summary>

/// <param name="listService">The SharePoint list service to execute the CAML against.</param>

/// <param name="strListName">The name of the list to execute the CAML against.</param>

/// <param name="elementLargeBatch">The CAML batch list of commands to be broken up.</param>

/// <param name="intBatchSize">The size of batches to use.  If unsure use 300, it seems to work fairly well.</param>

/// <returns>The results of all batched queries.</returns>

public static XmlNode ExecuteLargeQuery(

    Lists listService,

    string strListName,

    XmlElement elementLargeBatch,

    int intBatchSize

    ) {

     

    // calculate useful information

    int intMethodCount = elementLargeBatch.ChildNodes.Count;

    int intBatchCount = (int)Math.Ceiling((double)intMethodCount / (double)intBatchSize);

     

    // prepare xml documents for batches and results

    XmlDocument xmlDocBatch = new XmlDocument();

    XmlDocument xmlDocResults = new XmlDocument();

    XmlElement elementResults = xmlDocResults.CreateElement("Results");

   

    // for each batch

    for (int intCurrentBatch = 0; intCurrentBatch < intBatchCount; intCurrentBatch++) {

        int intMethodStart = intCurrentBatch * intBatchSize;

        int intMethodEnd = Math.Min(intMethodStart + intBatchSize - 1, intMethodCount - 1);

     

        XmlElement elementSmallBatch = ListHelper.CreateBatch(xmlDocBatch);

     

        // for each method in the batch

        for (int intCurrentMethod = intMethodStart; intCurrentMethod <= intMethodEnd; intCurrentMethod++) {

            XmlElement element = (XmlElement)elementLargeBatch.ChildNodes[intCurrentMethod];

            elementSmallBatch.AppendChild(xmlDocBatch.ImportNode(element, true));

        }

     

        // execute the batch

        XmlNode nodeBatchResult = listService.UpdateListItems(strListName, elementSmallBatch);

       

        // add the results of the batch into the results xml document

        foreach (XmlElement elementResult in nodeBatchResult.ChildNodes)

            elementResults.AppendChild(xmlDocResults.ImportNode(elementResult, true));

     

        // clean up

        xmlDocBatch.RemoveAll();

     

    }

     

    return elementResults;

}

Useful MSDN References

Incidentally, these MSDN references might be useful if you’re interested in more info:

Introduction to Collaborative Application Markup Language (CAML)

CAML Reference: Query Schema

CAML Reference: Batch Element