1

I am Trying to write an Dynamic Caml Query.

I did it like This:

CamlQuery qry = new CamlQuery();
        string qryStatus = "";
        string qryCategory = "";

        foreach (RiskStatus sta in filter.Status)
        {
            if (sta == null)
                continue;

            qryStatus = "<Eq><FieldRef Name='Status' /><Value Type='Text'>" + sta.Name + "</Value></Eq>" + qryStatus;
        }

        if (filter.Status.Length >= 2)
        {
            qryStatus = "<Or>" + qryStatus + "</Or>";
        }

        foreach (RiskCategory ctg in filter.Categories)
        {
            if (ctg == null)
                continue;

            qryCategory = "<Eq><FieldRef Name='Category' /><Value Type='Text'>" + ctg.Name + "</Value></Eq>" + qryCategory;
        }

        if (filter.Categories.Length >= 2)
        {
            qryCategory = "<Or>" + qryCategory + "</Or>";
        }

        qry.ViewXml = "<View Scope='RecursiveAll'>"
                    + "<Query>"
                    + "   <Where>"
                    + "     <And>"
                    + qryStatus
                    + qryCategory
                    + "     </And>"
                    + "   </Where>"
                    + "</Query>"
                    + "</View>";
        return qry;

I use this Test values:

CategoryArray[0] = new TestCategory { Name = "(2) TestCategory2" };
StatusArray[0] = new TestStatus { Name = "(1) TestStatus1" };

CategoryArray[1] = new TestCategory { Name = "(3) TestCategory3" };
StatusArray[1] = new TestStatus { Name = "(2) TestStatus2" };

This is the Result I get:

<View Scope='RecursiveAll'>
<Query>   
<Where>     
    <And>
        <Or>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus1</Value></Eq>
        </Or>
        <Or>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(3) TestCategory3</Value></Eq>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(2) TestCategory2</Value></Eq>
        </Or>     
    </And>   
</Where>
</Query>
</View>

It works fine But if I add a third value I get the exception "Cannot complete this action.\n\nPlease try again."

Added Value:

  StatusArray[2] = new TestStatus { Name = "(3) Status3" };

Result:

<View Scope='RecursiveAll'>
<Query>   
<Where>     
    <And>
        <Or>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus3</Value></Eq>
        </Or>
        <Or>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(3) TestCategory3</Value></Eq>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(2) TestCategory2</Value></Eq>
        </Or>     
    </And>   

What am I doing wrong? (This is my first time working with Caml Query)

  • There is one problem in your last caml query. You have to write</Eq> instead of </eq>. And you can not add 3 fieldref in OR tag. – Dikesh Gandhi Jun 9 '16 at 11:02
  • So how is it suppose to look like if I want to do it with 3 or more? – socramm Jun 9 '16 at 11:13
  • You can get some reference from here -stackoverflow.com/questions/6203821/… – Dikesh Gandhi Jun 9 '16 at 11:22
  • doing it like that will make it a lot complicated... I will try. Thanks. – socramm Jun 9 '16 at 11:48
3

I had similar kind of requirement, below is the code which I have used previously:

enum MergeType { Or, And };

private static string MergeCAMLConditions(List<string> conditions, MergeType type)
{
    if (conditions.Count == 0) return "";

    string typeStart = (type == MergeType.And ? "<And>" : "<Or>");
    string typeEnd = (type == MergeType.And ? "</And>" : "</Or>");

    // Build hierarchical structure
    while (conditions.Count >= 2)
    {
        List<string> complexConditions = new List<string>();

        for (int i = 0; i < conditions.Count; i+=2)
        {
            if (conditions.Count == i + 1) // Only one condition left
                complexConditions.Add(conditions[i]);
            else // Two condotions - merge
                complexConditions.Add(typeStart + conditions[i] + conditions[i + 1] + typeEnd);
            }

            conditions = complexConditions;
        }

        return conditions[0];
    }

You can call it using:

List<string> conditions = new List<string>();
conditions.Add("<Eq><FieldRef Name='Col1' /><Value Type='Text'>value1</Value></Eq>");
conditions.Add("<Eq><FieldRef Name='Col2' /><Value Type='Text'>value2</Value></Eq>");;

string merged = MergeCAMLConditions(conditions, MergeType.And);
1

CAML queries only accept two conditions for logical operators. So you can do

<And>
   <Eq>...</Eq>
   <Eq>...</Eq>
</And>

but not

<And>
   <Eq>...</Eq>
   <Eq>...</Eq>
   <Eq>...</Eq>
</And>

Solution is using two s:

<And>
  <And>
   <Eq>...</Eq>
   <Eq>...</Eq>
  </And>
   <Eq>...</Eq>
</And>
1

While writing CAML query, you must understand the architecture how it works.

It is pretty simple. You just have to do logical AND/OR operators.

Any operator must have 2 conditions to combine and test.

SCENARIO 1 : This is 1 Operator use with 2 conditions

 <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
 </Or>

SCENARIO 2 : This is 2 Operators use with 2 conditions From scenario 1 we got operator now along with that we will add another operator.

<And>
    <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
    </Or>
    <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus1</Value></Eq>                  
</And>

Here if you notice, <OR> is having two conditions inside it. and that makes 1 condition for outer <And>, so we added another condition block parallel to <OR> operator. Hence 2 conditions are available for <AND>.

Likewise you can build n number of nested conditions.

PS: If you have only 1 condition, it will not require any operator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.