{"id":190,"date":"2010-07-11T14:21:55","date_gmt":"2010-07-11T12:21:55","guid":{"rendered":"http:\/\/jsiegmund.wordpress.com\/2010\/07\/11\/constructing-dynamic-caml-queries-the-easy-way\/"},"modified":"2013-04-18T18:42:10","modified_gmt":"2013-04-18T17:42:10","slug":"constructing-dynamic-caml-queries-the-easy-way","status":"publish","type":"post","link":"http:\/\/blog.repsaj.nl\/index.php\/2010\/07\/constructing-dynamic-caml-queries-the-easy-way\/","title":{"rendered":"Constructing dynamic CAML queries: the easy way"},"content":{"rendered":"

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<\/span> 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.<\/p>\n

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<\/a>, which seemed to do exactly what I needed.<\/p>\n

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.<\/p>\n

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:<\/p>\n

Builder<\/span> camlBuilder = new<\/span>
\nBuilder<\/span>(U2U.SharePoint.CAML.Enumerations.CamlTypes<\/span>.Query);
\n<\/span><\/p>\n

Use camlBuilder.AddWhereField<\/span> to add a field filter to the Where clause. Usage:
\ncamlBuilder.AddWhereField(“FieldName”<\/span>, “Value”<\/span>, “Text”<\/span>, “Eq”<\/span>, out<\/span> addCombinerNode);
\n<\/span><\/p>\n

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?<\/p>\n

Okay; this generates CAML, like this:<\/p>\n

<Query><\/span>
\n\u00a0\u00a0<Where><\/span>
\n\u00a0\u00a0\u00a0\u00a0<Eq><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<FieldRef\u00a0Name=<\/span>“FieldName<\/span>“\u00a0\/><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<Value\u00a0Type=<\/span>“Text<\/span>“><\/span>FieldValue<\/Value><\/span>
\n\u00a0\u00a0\u00a0\u00a0<\/Eq><\/span>
\n\u00a0\u00a0<\/Where><\/span>
\n<\/Query><\/span>
\n<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

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<\/span> 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:<\/p>\n

<Query><\/span>\u00a0\u00a0\r\n  <\/span><\/span><\/span><\/code><Where><\/span>\u00a0\u00a0\u00a0\u00a0\r\n    <And><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n      <Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n       <FieldRef\u00a0Name=<\/span>\"FieldName1<\/span>\"\u00a0\/><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n       <Value\u00a0Type=<\/span>\"Text<\/span>\"><\/span>FieldValue<\/Value><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n     <\/Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n     <And><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n       <Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n         <FieldRef\u00a0Name=<\/span>\"FieldName2<\/span>\"\u00a0\/><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n         <Value\u00a0Type=<\/span>\"Text<\/span>\"><\/span>FieldValue<\/Value><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n       <\/Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n       <Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n         <FieldRef\u00a0Name=<\/span>\"FieldName3<\/span>\"\u00a0\/><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n         <Value\u00a0Type=<\/span>\"Text<\/span>\"><\/span>FieldValue<\/Value><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n       <\/Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n     <\/And><\/span>\u00a0\u00a0\u00a0\u00a0\r\n    <\/And><\/span>\u00a0\u00a0\r\n  <\/Where>\r\n<\/span><\/Query><\/span>\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/code><\/pre>\n
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.<\/p>\n
To cope with this, I wrote two methods which can help. The first one is this one:<\/p>\n

\nprivate<\/span>
\nXmlNode<\/span> ConstructAndOrTree(TreeType<\/span> andOr, string<\/span> fieldName, Stack<\/span><string<\/span>> values, string<\/span> fieldType, string<\/span> operatorTag, XmlNode<\/span> parentNode, Builder<\/span> camlBuilder)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nbool<\/span> addCombinerNode;
\n<\/span><\/p>\n

\n\/\/ when there are no values; return the rootNode<\/span>
\n<\/span><\/p>\n

\nif<\/span> (values.Count == 0)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span>
\nnull<\/span>;
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\n\/\/ when there’s just one value; append the node and return <\/span>
\n<\/span><\/p>\n

\nelse<\/span>
\nif<\/span> (values.Count == 1)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span> camlBuilder.AddWhereField(fieldName, values.Pop(), fieldType, operatorTag, out<\/span> addCombinerNode);
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\n\/\/ when there’s multiple values; construct and and\/or node; append one value and recursively call this method<\/span>
\n<\/span><\/p>\n

\nelse<\/span>
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nstring<\/span> andOrTag = andOr == TreeType<\/span>.And ? “And”<\/span> : “Or”<\/span>;
\n<\/span><\/p>\n

\nXmlNode<\/span> andOrNode = camlBuilder.AddAndOrNode(andOrTag, null<\/span>);
\n<\/span><\/p>\n
camlBuilder.AddWhereField(“FieldName”<\/span>, “Value”<\/span>, “Text”<\/span>, “Eq”<\/span>, out<\/span> addCombinerNode);
\n<\/span><\/p>\n
andOrNode.AppendChild(camlBuilder.AddWhereField(fieldName, values.Pop(), fieldType, operatorTag, out<\/span> addCombinerNode));
\n<\/span><\/p>\n
andOrNode.AppendChild(ConstructAndOrTree(andOr, fieldName, values, fieldType, operatorTag, andOrNode, camlBuilder));
\n<\/span><\/p>\n

\nreturn<\/span> andOrNode;
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n
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:<\/p>\n
\u00a0\u00a0\u00a0\u00a0<Or><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n      <Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n        <FieldRef\u00a0Name=<\/span>\"Title<\/span>\"\u00a0\/><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n        <Value\u00a0Type=<\/span>\"Text<\/span>\"><\/span>Value1<\/Value><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n      <\/Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n      <Or><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n        <Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n          <FieldRef\u00a0Name=<\/span>\"Title<\/span>\"\u00a0\/><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n          <Value\u00a0Type=<\/span>\"Text<\/span>\"><\/span>Value2<\/Value><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n        <\/Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n        <Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n          <FieldRef\u00a0Name=<\/span>\"Title<\/span>\"\u00a0\/><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n          <Value\u00a0Type=<\/span>\"Text<\/span>\"><\/span>Value3<\/Value><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n        <\/Eq><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n      <\/Or><\/span>\u00a0\u00a0\u00a0\u00a0\r\n    <\/Or><\/span><\/span>\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/code><\/pre>\n
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:<\/p>\n

\nprivate<\/span>
\nXmlNode<\/span> CombineNodes(TreeType<\/span> andOr, XmlNode<\/span> node1, XmlNode<\/span> node2, Builder<\/span> camlBuilder)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\n\/\/ create and and\/or node<\/span>
\n<\/span><\/p>\n

\nstring<\/span> andOrTag = andOr == TreeType<\/span>.And ? “And”<\/span> : “Or”<\/span>;
\n<\/span><\/p>\n

\nXmlNode<\/span> andOrNode = camlBuilder.AddAndOrNode(andOrTag, null<\/span>);
\n<\/span><\/p>\n

\n\/\/ when a node is null; there’s nothing to combine<\/span>
\n<\/span><\/p>\n

\nif<\/span> (node1 == null<\/span>)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span> node2;
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\nelse<\/span>
\nif<\/span> (node2 == null<\/span>)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span> node1;
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\nelse<\/span>
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n
andOrNode.AppendChild(node1);
\n<\/span><\/p>\n
andOrNode.AppendChild(node2);
\n<\/span><\/p>\n

\nreturn<\/span> andOrNode;
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\nprivate<\/span>
\nXmlNode<\/span> CombineNodes(TreeType<\/span> andOr, Builder<\/span> camlBuilder, Stack<\/span><XmlNode<\/span>> nodes)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nif<\/span> (nodes.Count == 0)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span>
\nnull<\/span>;
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\nelse<\/span>
\nif<\/span> (nodes.Count == 1)
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span> nodes.Pop();
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n

\nelse<\/span>
\n<\/span><\/p>\n
{
\n<\/span><\/p>\n

\nreturn<\/span> CombineNodes(andOr, nodes.Pop(), CombineNodes(andOr, camlBuilder, nodes), camlBuilder);
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n
}
\n<\/span><\/p>\n
Use it like this:<\/p>\n
CombineNodes(TreeType<\/span>.And, camlBuilder, nodes);
\n<\/span><\/p>\n
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.<\/p>\n
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:<\/p>\n
Builder<\/span> camlBuilder = new<\/span>
\nBuilder<\/span>(U2U.SharePoint.CAML.Enumerations.CamlTypes<\/span>.Query);
\n<\/span><\/p>\n
bool<\/span> addCombinerNode;
\n<\/span><\/p>\n
\/\/ stack for multiple XML trees
\n<\/span><\/p>\n
Stack<\/span><XmlNode<\/span>> nodes = new<\/span>
\nStack<\/span><XmlNode<\/span>>();
\n<\/span><\/p>\n
\/\/ add parameters trees when parameters have been set
\n<\/span><\/p>\n
if<\/span> (taskStates != null<\/span> && taskStates.Length > 0)
\n<\/span><\/p>\n
nodes.Push(ConstructAndOrTree(TreeType<\/span>.Or, “TaskState”<\/span>, new<\/span>
\nStack<\/span><string<\/span>>(taskStates), “Choice”<\/span>, “Eq”<\/span>, camlBuilder.WhereNode.FirstChild, camlBuilder));
\n<\/span><\/p>\n
if<\/span> (taskTypes != null<\/span> && taskTypes.Length > 0)
\n<\/span><\/p>\n
nodes.Push(ConstructAndOrTree(TreeType<\/span>.Or, “TaskType”<\/span>, new<\/span>
\nStack<\/span><string<\/span>>(taskTypes), “Choice”<\/span>, “Eq”<\/span>, camlBuilder.WhereNode.FirstChild, camlBuilder));
\n<\/span><\/p>\n
\/\/ always add content type query parameter
\n<\/span><\/p>\n
nodes.Push(camlBuilder.AddWhereField(“ContentType”<\/span>, “Task”<\/span>, “Computed”<\/span>, “Eq”<\/span>, out<\/span> addCombinerNode));
\n<\/span><\/p>\n
\/\/ recursively combine trees \/ nodes with And nodes
\n<\/span><\/p>\n
XmlNode<\/span> tree = CombineNodes(TreeType<\/span>.And, camlBuilder, nodes);
\n<\/span><\/p>\n
\/\/ set the resulting XML tree as child of the Where node
\n<\/span><\/p>\n
camlBuilder.WhereNode.RemoveAll();
\n<\/span><\/p>\n
camlBuilder.WhereNode.AppendChild(tree);
\n<\/span><\/p>\n
\/\/ add the view fields
\n<\/span><\/p>\n
camlBuilder.AddViewField(“Title”<\/span>);
\n<\/span><\/p>\n
camlBuilder.AddViewField(“ID”<\/span>);
\n<\/span><\/p>\n
\/\/ build SPSiteDataQuery object and set members
\n<\/span><\/p>\n
SPSiteDataQuery<\/span> apQuery = new<\/span>
\nSPSiteDataQuery<\/span>();
\n<\/span><\/p>\n
apQuery.Lists = “<Lists ServerTemplate=’100′ \/>”<\/span>;
\n<\/span><\/p>\n
apQuery.Webs = “<Webs Scope=’SiteCollection’ \/>”<\/span>;
\n<\/span><\/p>\n
apQuery.ViewFields = camlBuilder.ViewFieldsNode.InnerXml;
\n<\/span><\/p>\n
apQuery.Query = camlBuilder.WhereNode.OuterXml;
\n<\/span><\/p>\n
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!<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[34],"tags":[7,13,30],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3KFR1-34","_links":{"self":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/posts\/190"}],"collection":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/comments?post=190"}],"version-history":[{"count":0,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/posts\/190\/revisions"}],"wp:attachment":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/media?parent=190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/categories?post=190"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/tags?post=190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}