CAML Query with AND OR

Question

I'm struggling to get a simple query working and would appreciate any assistance in pointing out my error/s.

I need to return all "Chart Type" that equal "Jacket" for "2016" in both "Mar" and "Feb".

    <Query>
        <Where>
            <And>
                <Eq>
                    <FieldRef Name='Chart_x0020_Type' />
                    <Value Type='Choice'>Jacket</Value>
                </Eq>
                <Eq>
                    <FieldRef Name='Year' />
                    <Value Type='Text'>2016</Value>
                </Eq>
                <And>
                    <Or>
                        <Eq>
                            <FieldRef Name="Month" />
                            <Value Type='Text'>Mar</Value>
                        </Eq>
                        <Eq>
                            <FieldRef Name="Month" />
                            <Value Type='Text'>Feb</Value>
                        </Eq>
                    </Or>
                </And>
            </And>
        </Where>
    </Query>

Using a SP CAML builder I only get errors so I'm sure I've approached this incorrectly.

Thanks.

Answer 1

1. Your first <And> have three child clauses. That's not permitted. One <And> (or one <Or>) must have exactly two children. You then must nest all clauses so you only have two clauses in any <And> or <Or>.
2. If you run this query from an SPQuery class (i.e. you set your CAML string into mySPQuery.Query property), you must get rid of the <Query> tag. The first level must be <Where>.

So, your CAML query must be rewritten as this:

<Where>
  <And>
    <Eq>
      <FieldRef Name='Chart_x0020_Type' />
      <Value Type='Choice'>Jacket</Value>
    </Eq>
    <And>
      <Eq>
        <FieldRef Name='Year' />
        <Value Type='Text'>2016</Value>
      </Eq>
      <Or>
        <Eq>
          <FieldRef Name="Month" />
          <Value Type='Text'>Mar</Value>
        </Eq>
        <Eq>
          <FieldRef Name="Month" />
          <Value Type='Text'>Feb</Value>
        </Eq>
      </Or>
    </And>
  </And>
</Where>

As you can see:

• The first <And> encapsulates one <Eq> and another <And>.
• That second <And> has also two children: another <Eq> and a <Or>.
• Finally the <Or>has two children itself: the two inner <Eq>.

Answer 2

Look this,

<Query>
<Where>
    <And>
        <Eq>
            <FieldRef Name='Chart_x0020_Type' />
            <Value Type='Choice'>Jacket</Value>
        </Eq>
    <And>
        <Eq>
            <FieldRef Name='Year' />
            <Value Type='Text'>2016</Value>
        </Eq>
    <Or>
        <Eq>
            <FieldRef Name="Month" />
            <Value Type='Text'>Mar</Value>
        </Eq>
        <Eq>
            <FieldRef Name="Month" />
            <Value Type='Text'>Feb</Value>
        </Eq>
    </Or>
    </And>
</And>
</Where>
</Query>

Remove "Query" tag from above query before assign it to SPQuery.query

Answer 3

Try this query

 <Query>
    <Where>
       <And>
         <And>
             <Eq>
                <FieldRef Name='Chart_x0020_Type' />
                <Value Type='Choice'>Jacket</Value>
             </Eq>
             <Eq>
                <FieldRef Name='Year' />
                <Value Type='Text'>2016</Value>
             </Eq>
         </And>
         <Or>
             <Eq>
                 <FieldRef Name="Month" />
                 <Value Type='Text'>Mar</Value>
             </Eq>
             <Eq>
                 <FieldRef Name="Month" />
                 <Value Type='Text'>Feb</Value>
             </Eq>
         </Or>
       </And>
    </Where>
 </Query>

Design your CAML query using tool to get exact query which you wants to achive..

U2U Caml Query Builder