Constructing dynamic CAML queries: the easy way
For a project I’m working on, I needed a way to roll-up data across multiple sites. There aren’t much ways of doing that, but one good way is using the SPSiteDataQuery object. This object, which can be used with the object model (not the client ones unfortunately), can be used to fire queries to SharePoint which get data from multiple lists and multiple sites at once. You can specify which list types, content types and what fields to retrieve and, which this blog post is about, you can create a WHERE query to filter down the data.
When you write queries for the SPSiteDataQuery object, you have to use CAML (Collaborative Application Markup Language). This comes down to writing properly formed XML in which you, for instance, specify which query you want to execute. Now sometimes you can just write a static query and use that, but other times you’ll need something a bit more dynamic. I was struggling with this, because I didn’t find a proper way of doing this. Until I ran into the U2U CAML Query Builder, which seemed to do exactly what I needed.
The great thing about this tool is that you, as a developer, can reuse the DLL’s in your own project. And with that option, writing CAML suddenly becomes a lot easier.
I’ll only cover the Where query here, but you can use it for other CAML things too. Here are the most important thing to know:
Builder camlBuilder = new
Builder(U2U.SharePoint.CAML.Enumerations.CamlTypes.Query);
Use camlBuilder.AddWhereField to add a field filter to the Where clause. Usage:
camlBuilder.AddWhereField(“FieldName”, “Value”, “Text”, “Eq”, out addCombinerNode);
Now you probably wonder what the addCombinerNode parameter is for; I honestly don’t know either. But since you don’t really need it for this purpose, who cares?
Okay; this generates CAML, like this:
<Query>
<Where>
<Eq>
<FieldRef Name=“FieldName“ />
<Value Type=“Text“>FieldValue</Value>
</Eq>
</Where>
</Query>
Cool, saves some work. Now to get this dynamic, you need to know a few things about CAML. Combining multiple fields is done with And and Or nodes. Each And / Or node must have exactly two Boolean parts. The above CAML fragment (starting at Eq) represents one single Boolean value. So when you have two Eq’s, you can wrap those inside an And / Or node. If you have three though, you’ll need two And / Or nodes; one with two Eq’s in it, and one with one Eq and the other And / Or nore (since that represents a Boolean value too). Let me illustrate this with an example:
<Query><Where> <And> <Eq> <FieldRef Name="FieldName1" /> <Value Type="Text">FieldValue</Value> </Eq> <And> <Eq> <FieldRef Name="FieldName2" /> <Value Type="Text">FieldValue</Value> </Eq> <Eq> <FieldRef Name="FieldName3" /> <Value Type="Text">FieldValue</Value> </Eq> </And> </And> </Where> </Query>
So the main problem with constructing this CAML is that you need to ‘look ahead’; you can add the first node, but when a second comes in, you need to wrap that and the previous one with an And/Or.
To cope with this, I wrote two methods which can help. The first one is this one:
private
XmlNode ConstructAndOrTree(TreeType andOr, string fieldName, Stack<string> values, string fieldType, string operatorTag, XmlNode parentNode, Builder camlBuilder)
{
bool addCombinerNode;
// when there are no values; return the rootNode
if (values.Count == 0)
{
return
null;
}
// when there’s just one value; append the node and return
else
if (values.Count == 1)
{
return camlBuilder.AddWhereField(fieldName, values.Pop(), fieldType, operatorTag, out addCombinerNode);
}
// when there’s multiple values; construct and and/or node; append one value and recursively call this method
else
{
string andOrTag = andOr == TreeType.And ? “And” : “Or”;
XmlNode andOrNode = camlBuilder.AddAndOrNode(andOrTag, null);
camlBuilder.AddWhereField(“FieldName”, “Value”, “Text”, “Eq”, out addCombinerNode);
andOrNode.AppendChild(camlBuilder.AddWhereField(fieldName, values.Pop(), fieldType, operatorTag, out addCombinerNode));
andOrNode.AppendChild(ConstructAndOrTree(andOr, fieldName, values, fieldType, operatorTag, andOrNode, camlBuilder));
return andOrNode;
}
}
This one is used for multi-value parameters. For instance, suppose you want to query items where the Title equals “Value1” or “Value2″ or Value3”. Use this method, pass in a stack with those three values and it will return the following XML tree:
<Or>
<Eq>
<FieldRef Name="Title" />
<Value Type="Text">Value1</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name="Title" />
<Value Type="Text">Value2</Value>
</Eq>
<Eq>
<FieldRef Name="Title" />
<Value Type="Text">Value3</Value>
</Eq>
</Or>
</Or>
So you now can build these trees dynamically for your multi-valued parameters. But, you still need to combine all the trees into one big three. That’s where this method comes in:
private
XmlNode CombineNodes(TreeType andOr, XmlNode node1, XmlNode node2, Builder camlBuilder)
{
// create and and/or node
string andOrTag = andOr == TreeType.And ? “And” : “Or”;
XmlNode andOrNode = camlBuilder.AddAndOrNode(andOrTag, null);
// when a node is null; there’s nothing to combine
if (node1 == null)
{
return node2;
}
else
if (node2 == null)
{
return node1;
}
else
{
andOrNode.AppendChild(node1);
andOrNode.AppendChild(node2);
return andOrNode;
}
}
private
XmlNode CombineNodes(TreeType andOr, Builder camlBuilder, Stack<XmlNode> nodes)
{
if (nodes.Count == 0)
{
return
null;
}
else
if (nodes.Count == 1)
{
return nodes.Pop();
}
else
{
return CombineNodes(andOr, nodes.Pop(), CombineNodes(andOr, camlBuilder, nodes), camlBuilder);
}
}
Use it like this:
CombineNodes(TreeType.And, camlBuilder, nodes);
The first parameter is an enum which represents either And or Or. The second is the U2U CAML query builder object, the third is again a stack, but this time with all XmlNodes representing the different XML trees. Now this comines everything with either And or Or nodes. Perhaps you need a bit more influence on that; in that case just skip the stack approach and call the upper CombineNodes method yourself.
So with this, you can create (like I’ve done) a webservice which serves all items from a site collection rolled up; filtered by the parameters you want. Building the CAML becomes a lot easier, my code for instance is the following:
Builder camlBuilder = new
Builder(U2U.SharePoint.CAML.Enumerations.CamlTypes.Query);
bool addCombinerNode;
// stack for multiple XML trees
Stack<XmlNode> nodes = new
Stack<XmlNode>();
// add parameters trees when parameters have been set
if (taskStates != null && taskStates.Length > 0)
nodes.Push(ConstructAndOrTree(TreeType.Or, “TaskState”, new
Stack<string>(taskStates), “Choice”, “Eq”, camlBuilder.WhereNode.FirstChild, camlBuilder));
if (taskTypes != null && taskTypes.Length > 0)
nodes.Push(ConstructAndOrTree(TreeType.Or, “TaskType”, new
Stack<string>(taskTypes), “Choice”, “Eq”, camlBuilder.WhereNode.FirstChild, camlBuilder));
// always add content type query parameter
nodes.Push(camlBuilder.AddWhereField(“ContentType”, “Task”, “Computed”, “Eq”, out addCombinerNode));
// recursively combine trees / nodes with And nodes
XmlNode tree = CombineNodes(TreeType.And, camlBuilder, nodes);
// set the resulting XML tree as child of the Where node
camlBuilder.WhereNode.RemoveAll();
camlBuilder.WhereNode.AppendChild(tree);
// add the view fields
camlBuilder.AddViewField(“Title”);
camlBuilder.AddViewField(“ID”);
// build SPSiteDataQuery object and set members
SPSiteDataQuery apQuery = new
SPSiteDataQuery();
apQuery.Lists = “<Lists ServerTemplate=’100′ />”;
apQuery.Webs = “<Webs Scope=’SiteCollection’ />”;
apQuery.ViewFields = camlBuilder.ViewFieldsNode.InnerXml;
apQuery.Query = camlBuilder.WhereNode.OuterXml;
There you have it; building complicated CAML queries in a simple way; special thanks to the guys at U2U for providing their CAML builder for free!
Leave a Comment