Building CAML query with AND and Order by Clause

Question

Hi I am trying to build a query using caml query builder - Below is the query (The query = get the values only whose address = 1234st AND LastName = Doe) - I believe the placement of AND operator is giving me error, tried various permutation by changing the position of AND operators but no luck - any suggestions?

<OrderBy>
 <FieldRef Name = "ID" Ascending = "TRUE"/>
</OrderBy>
<Where>
 <And>
  <Eq>
   <FieldRef Name = "HomeType"><Value Type="Choice">Condo</FieldRef>
  </Eq>
 <And>
  <Eq>
   <FieldRef Name = "LastName"><Value Type="Text">Doe</FieldRef>
  </Eq>
 </And>
 </And>
</Where>

Eric Alexander · Accepted Answer · 2016-07-11T23:34:32.020

20

Try this:

<OrderBy>
<FieldRef Name="ID" Ascending="TRUE"/>
</OrderBy>
<Where>
 <And>
  <Eq>
   <FieldRef Name="HomeType"/><Value Type="Choice">Condo</Value>
  </Eq>
  <Eq>
   <FieldRef Name="LastName"/><Value Type="Text">Doe</Value>
  </Eq>
 </And>
</Where>

edited Jul 11 '16 at 23:34

answered Jul 15 '13 at 03:52

Eric Alexander

43,293
10
53
93

Also no spaces around the equal sign. Name = " becomes Name=". – Kit Menke Jul 15 '13 at 03:58
cleaned those up – Eric Alexander Jul 15 '13 at 05:32
1

Spaces around the equal sign i.e. Name = " do not create any issue. – Altaf Patel Mar 15 '15 at 12:53
<FieldRef> don't want a body!!! The answer of @ilMattion is right. – Emaborsa Jul 11 '16 at 08:05
@Emaborsa what are you referring to? – Eric Alexander Jul 11 '16 at 11:56
You wrote <FieldRef Name="HomeType"><Value Type="Choice">Condo</Value></FieldRef>. This is wrong, The right format is <FieldRef Name = "HomeType" /><Value Type="Choice">Condo</Value> – Emaborsa Jul 11 '16 at 17:44
You're right, I double checked it again BIWUG. I've edited the answer to reflect that. tyvm. – Eric Alexander Jul 11 '16 at 23:35

score 4 · Answer 2 · answered Jan 14 '15 at 17:56

4

I think that this code:

<Eq>
 <FieldRef Name = "HomeType"><Value Type="Choice">Condo</FieldRef>
</Eq>

must be like that:

<Eq>
   <FieldRef Name = "HomeType" /><Value Type="Choice">Condo</Value>
</Eq>

answered Jan 14 '15 at 17:56

ilMattion

275
5
13

score 1 · Answer 3 · answered Sep 23 '14 at 04:09

You had an extra <And> and </And>. Just remember that you only need a set of those around every 2 fields (or groups) you are comparing. Ex.

<Where>
 <And>
     <And>
       <Eq>
         <FieldRef Name="HomeType"><Value Type="Choice">Condo</FieldRef>
       </Eq>
       <Eq>
         <FieldRef Name="LastName"><Value Type="Text">Doe</FieldRef>
       </Eq>
     </And>
     <Eq>
        <FieldRef Name="ID"><Value Type="Integer">1</FieldRef>
     </Eq>
   </And>
</Where>

And this is also valid. Notice the grouping of two items per 'And', then an 'And' between the two groups:

<Where>
 <And>
     <And>
       <Eq>
         <FieldRef Name="HomeType"><Value Type="Choice">Condo</FieldRef>
       </Eq>
       <Eq>
         <FieldRef Name="LastName"><Value Type="Text">Doe</FieldRef>
       </Eq>
     </And>
     <And>
      <Eq>
        <FieldRef Name="Rooms"><Value Type="Integer">2</FieldRef>
      </Eq>         
      <Eq>
        <FieldRef Name="Location"><Value Type="Text">Brooklyn</FieldRef>
      </Eq>
     </And>
   </And>
</Where>

score 1 · Answer 4 · answered Jul 15 '13 at 06:34

1

Try using the CAML query buider from U2U, it's really helpful. It will build the queries for you

answered Jul 15 '13 at 06:34

Mats Lindberg

19
1

U2U CAML query builder link to download is dead now. I use CAML designer (freeware) from http://www.camldesigner.com/. Works for both 2010 and 2013 and pretty intuitive way to build queries. – Falak Mahmood Jul 15 '13 at 08:25
CAML query builder links question is answered: http://sharepoint.stackexchange.com/a/72165/62 – SPDoctor Jul 16 '13 at 08:30

Building CAML query with AND and Order by Clause

4 Answers4