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? – user53548 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. – user53548 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);
| improve this answer | |
0

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>
| improve this answer | |
0

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.

| improve this answer | |

Your Answer

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