How to add new filters to CAML queries in SharePoint 2007

Tuesday, June 15, 2010

One flexibility SharePoint has is CAML (Collaborative Application Markup Language).

CAML it's a markup language like html that allows developers to do queries against SharePoint lists, it's syntax is very easy to understand and it allows to add logical conditions like Where, Contains, And, Or, etc, just like a SQL Query.

For one of our projects we have the need to do a filter on SharePoint views, the problem here is that the view it's a list containing a CAML Query with the filters the view may have, so in order to filter the view that's already been filtered before, we need to append our filters to the existing CAML Query.

That's not a trivial task because the where statement in a CAML Query it's like this:

If we want to add a new logical operator, like an OR it's not just as simple as to append the OR expression like the following example:

But instead the correct query would be:

Notice that the <Filter# /> tags are for explanation purpose only.

In order to solve this problem we created a simple component, it has a method that receives the current query (could be an empty query also) and appends the expression you want to that query.

Example:

string currentQuery = @"
<Where>
   <And>
    <Contains><FieldRef Name='Title' /><Value Type='Text'>A</Value></Contains>
    <Contains><FieldRef Name='Title' /><Value Type='Text'>B</Value></Contains>
</And>
</Where>";

currentQuery = CAMLQueryBuilder.AppendQuery(
    currentQuery,
    "<Contains><FieldRef Name='Title' /><Value Type='Text'>C</Value></Contains>",
    CAMLQueryBuilder.Operators.Or);

The fist parameter this function receives it's the actual query, the second it's the filter you want to add, and the third it's the logical operator, so basically in this query we want all the items that the title contains: the character A and B or the ones that contains the character C.

The result query is:

The code:

First of all we have an enumerator inside the CAMLQueryBuilder class that has the two possible

Options And, Or.

public enum Operators { And, Or }

Then we have the main method that's the one that performs the append of the filters.

public static string AppendQuery(string containerQuery, string logicalExpression, Operators logicalOperator){

In this method the first we do is create a new XmlDocument and wrap the current query (that may be empty) with a "<Query></Query>" tag, because the query that comes with the view doesn't have a root element and the XmlDocument must be a well formatted xml.

XmlDocument queryDoc = new XmlDocument();

queryDoc.LoadXml("<Query>" + containerQuery + "</Query>");

The next step is to create a new XmlDocument containing the logical expression that has the filter needed.

XmlDocument logicalExpressionDoc = new XmlDocument();

logicalExpressionDoc.LoadXml("<root>" + logicalExpression + "</root>");

In these next four lines we extract the expression from the recently created XmlDocument and create an XmlElement.

XmlElement expressionElTemp = (XmlElement)logicalExpressionDoc.SelectSingleNode("/root/*");

XmlElement expressionEl = queryDoc.CreateElement(expressionElTemp.Name);

expressionEl.InnerXml = expressionElTemp.InnerXml;

Below are the main steps in the component logic. The first "if" checks if the actual query doesn't contains a "Where" clause. In case there's no "Where" we add it and append the expression.

In case that there's already a "Where" clause, we get the entire statement that's inside the "Where" and reorder the query removing and appending elements to form the correct query, that will finally filter the list.

XmlElement whereEl;

if (!containerQuery.Contains("Where"))

{

queryDoc.FirstChild.AppendChild(queryDoc.CreateElement("Where"));

queryDoc.SelectSingleNode("/Query/Where").AppendChild(expressionEl);

}

else

{

whereEl = (XmlElement)queryDoc.SelectSingleNode("/Query/Where");

if (!containerQuery.Contains("<And>") && !containerQuery.Contains("<Or>"))

{

XmlElement operatorEl = queryDoc.CreateElement(GetName(logicalOperator));

XmlElement existingExpression = (XmlElement)whereEl.SelectSingleNode("/Query/Where/*");

whereEl.RemoveChild(existingExpression);

operatorEl.AppendChild(existingExpression);

operatorEl.AppendChild(expressionEl);

whereEl.AppendChild(operatorEl);

}

else

{

XmlElement operatorEl = queryDoc.CreateElement(GetName(logicalOperator));

XmlElement existingOperator = (XmlElement)whereEl.SelectSingleNode("/Query/Where/*");

whereEl.RemoveChild(existingOperator);

operatorEl.AppendChild(existingOperator);

operatorEl.AppendChild(expressionEl);

whereEl.AppendChild(operatorEl);

}

return queryDoc.FirstChild.InnerXml

}

Finally the GetName method converts the Enum option to his string equivalent.

private static string GetName(Operators logicalOperator)

{

return Enum.GetName(typeof(Operators), logicalOperator);

}

This component helped our team a lot using SharePoint 2007 and modifying the queries, but now in SharePoint 2010; that wouldn't be needed because of the incorporation of LINQ to SharePoint. This new feature enables the developers to do typed queries against SharePoint lists without the need of writing any CAML code. But there is still much development to the 2007 version so I hope this information is useful for other members.

Post written by Sebastian Rodriguez - Portals and Collaboration Solutions @ UruIT

No Comments