CAML query with nested ANDs and ORs for multiple fields

Question

I am working on proof-of-concept code to dynamically generate CAML based on keywords provided to a highly-specific search web service that I am writing  I am not using the SharePoint-provided search web service for this proof  I have done so already for what I am trying to achieve  From all of my research  I cannot find a close example for what I am trying to achieve  which is to check multiple fields for multiple values  Yes  I have looked on SO already for my answer  including this one  Need help on building CAML Query   With that said  if it is possible  how can the following SQL-like query be written in CAML   SELECT FirstName  LastName  Description  Profile FROM SomeFakeTable WHERE  FirstName    John  OR LastName    John  OR Description    John  OR Profile  John     AND  FirstName    Doe  OR LastName    Doe  OR Description    Doe  OR Profile  Doe     AND  FirstName    123  OR LastName    123  OR Description    123  OR Profile  123

User · Accepted Answer

Since you are not allowed to put more than two conditions in one condition group (And | Or) you have to create an extra nested group (MSDN). The expression A AND B AND C looks like this:

<And>
    A
    <And>
        B
        C
    </And>
</And>

Your SQL like sample translated to CAML (hopefully with matching XML tags ;) ):

<Where>
    <And>
        <Or>
            <Eq>
                <FieldRef Name='FirstName' />
                <Value Type='Text'>John</Value>
            </Eq>
            <Or>
                <Eq>
                    <FieldRef Name='LastName' />
                    <Value Type='Text'>John</Value>
                </Eq>
                <Eq>
                    <FieldRef Name='Profile' />
                    <Value Type='Text'>John</Value>
                </Eq>
            </Or>
        </Or>
        <And>       
            <Or>
                <Eq>
                    <FieldRef Name='FirstName' />
                    <Value Type='Text'>Doe</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name='LastName' />
                        <Value Type='Text'>Doe</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='Profile' />
                        <Value Type='Text'>Doe</Value>
                    </Eq>
                </Or>
            </Or>
            <Or>
                <Eq>
                    <FieldRef Name='FirstName' />
                    <Value Type='Text'>123</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name='LastName' />
                        <Value Type='Text'>123</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='Profile' />
                        <Value Type='Text'>123</Value>
                    </Eq>
                </Or>
            </Or>
        </And>
    </And>
</Where>

User · Answer

You can try U2U Query Builder http   www u2u net res Tools CamlQueryBuilder aspx you can use their API U2U SharePoint CAML Server dll and U2U SharePoint CAML Client dll  I didn t use them but I m sure it will help you achieving your task

User · Answer

This code will dynamically generate the expression for you with the nested clauses  I have a scenario where the number of  OR  s was unknown  so I m using the below  Usage           private static void Main string   args                        var query   new PropertyString    lt Query gt  lt Where gt   WhereClauses   lt  Where gt  lt  Query gt                 var whereClause                   new PropertyString    lt Eq gt  lt FieldRef Name  ID   gt  lt Value Type  Counter  gt   NestClauseValue   lt  Value gt  lt  Eq gt                 var andClause   new PropertyString   lt Or gt   FirstExpression    SecondExpression   lt  Or gt                  string   values     1    2    3    4    5    6                 query  WhereClauses     NestEq whereClause  andClause  values                Console WriteLine query               And here s the code      private static string MakeExpression PropertyString nestClause  string value                        var expr   nestClause New                expr  NestClauseValue     value              return expr ToString                            lt summary gt              Recursively nests the clause with the nesting expression  until nestClauseValue is empty               lt  summary gt               lt param name  whereClause  gt  A property string in the following format   lt Eq gt  lt FieldRef Name  Title   gt  lt Value Type  Text  gt   NestClauseValue   lt  Value gt  lt  Eq gt     lt  param gt               lt param name  nestingExpression  gt  A property string in the following format   lt And gt   FirstExpression    SecondExpression   lt  And gt   lt  param gt               lt param name  nestClauseValues  gt A string value which NestClauseValue will be filled in with  lt  param gt          public static string NestEq PropertyString whereClause  PropertyString nestingExpression  string   nestClauseValues  int pos 0                        if  pos  gt  nestClauseValues Length                                return                                if  nestClauseValues Length    1                                return MakeExpression whereClause  nestClauseValues 0                               var expr   nestingExpression New                if  pos    nestClauseValues Length - 2                                expr  FirstExpression     MakeExpression whereClause  nestClauseValues pos                    expr  SecondExpression     MakeExpression whereClause  nestClauseValues pos   1                    return expr ToString                              else                               expr  FirstExpression     MakeExpression whereClause  nestClauseValues pos                    expr  SecondExpression     NestEq whereClause  nestingExpression  nestClauseValues  pos   1                   return expr ToString                                            public class PropertyString               private string  propStr           public PropertyString New                         return new PropertyString  propStr                       public PropertyString string propStr                         propStr   propStr               properties   new Dictionary lt string  string gt                        private Dictionary lt string  string gt   properties          public string this string key                        get                               return  properties ContainsKey key     properties key    string Empty                            set                               if   properties ContainsKey key                                          properties key    value                                    else                                        properties Add key  value                                                             lt summary gt              Replaces properties in the format   propertyName   in the source string with values from KeyValuePairPropertiesDictionarysupplied dictionary nce you ve set a property it s replaced in the string and you               lt  summary gt               lt param name  originalStr  gt  lt  param gt               lt param name  keyValuePairPropertiesDictionary  gt  lt  param gt               lt returns gt  lt  returns gt          public override string ToString                         string modifiedStr    propStr              foreach  var keyvaluePair in  properties                                modifiedStr   modifiedStr Replace        keyvaluePair Key         keyvaluePair Value                              return modifiedStr

[sharepoint] CAML query with nested ANDs and ORs for multiple fields

Examples related to sharepoint

Examples related to caml

Examples related to nested-query