2

I am new to CAML query and as much as I have expected it to be simple like SQL I have found it quite frustrating to deal with. I have a simple query SQL query I am trying to translate into CAML but do not receive the expected result.

SQL: Select column1, column2, column3, column4, column5 from Table1 where column1 = 'Bob' and column2 = 'Marley';

My CAML query is: <Query><Where><And><Eq><FieldRef Name=\'Column1\'/><Value Type=\'Choice\'>Bob</Value></Eq><Eq><FieldRef Name=\'Column2/><Value Type=\'Choice\'>Marley</Value></Eq></And><Eq><FieldRef Name=\'Column3\'/></Eq><Eq><FieldRef Name=\'Column4\'/></Eq><Eq><FieldRef Name=\'Column5\'/></Eq>

Any help is appreciated. Thanks.

5 Answers 5

2

Assuming column1 and column2 are choice columns, the query should be:

<Query>
   <Where>
      <And>
         <Eq>
            <FieldRef Name='column1' />
            <Value Type='Choice'>Bob</Value>
         </Eq>
         <Eq>
            <FieldRef Name='column2' />
            <Value Type='Choice'>Marley</Value>
         </Eq>
      </And>
   </Where>
</Query>

You should also take into consideration that CAML field names will be case sensitive and the display name may not be the actual column name (for example "my column" would be "my_x0200_column").

You may want to check out the CAML query builder that will let you connect to a site, build the query using a wizard, show you the resultant CAML, and even let you test it: http://www.u2u.be/Software

3
  • Hi Jared I just tried your suggestion and I keep getting the same result as I was before - the query just pulls everything without filtering out based on the criteria. I haven't installed the CAML query helper yet as it has not been added to my company's list of accepted software. Hopefully it gets added soon and save me all the hassles of CAML querying manually. Apr 10, 2014 at 3:20
  • How are you executing the CAML? Are you using the server object model or the client object model? Also what version of SharePoint are you running? Apr 10, 2014 at 14:16
  • I'm using the client object model and running SharePoint 2010 for now. But I am all set now. Thanks to you guys for all the answers - I now have a better understanding of CAML. Apr 10, 2014 at 14:45
3

When building CAML query it is not possible to specify multiple conditions in logical And & Or operators because logical operators only takes 2 conditions.

Read more here: http://buli.waw.pl/caml-query-multiple-conditions-in-and-or-clause/

1
<Query><Where><And><Eq><FieldRef Name='column1' /><Value Type='Choice'>Bob</Value></Eq><Eq><FieldRef Name='column2' /><Value Type='Choice'>Marley</Value></Eq></And></Where></Query>

Thats It.

The fields you're selecting aren't specified in the Query itself, they're specified in the ViewFields property. Try a tool like this to help you in the future: http://spcamlqueryhelper.codeplex.com/

1

There are several errors in the code you pasted.

Are you trying to use SPQuery object to do the query? If that's the case, try this out:

 SPList myList = SPContext.Current.Web.TryGetList("MyListTitle");

 SPQuery query = new SPQuery();

 query.Query = @"
 <Where>
  <And>
   <Eq><FieldRef Name='Column1' /><Value Type='Choice'>Bob</Value></Eq>
   <Eq><FieldRef Name='Column2' /><Value Type='Choice'>Marley</Value></Eq>
  </And>
 </Where>";

 query.ViewFields = "<FieldRef Name='Column1' /><FieldRef Name='Column2' /><FieldRef Name='Column3' /><FieldRef Name='Column4' /><FieldRef Name='Column5'/>";

 SPListItemCollection items = myList.GetItems(query);
1
  • No I'm actually running my code in developer console for testing purposes as I am using javascript to retrieve list items. Apr 10, 2014 at 3:10
1

For JavaScript -

var query = new SP.CamlQuery();
query.set_viewXml("<Query><Where><And><Eq><FieldRef Name='column1' /><Value Type='Choice'>Bob</Value></Eq><Eq><FieldRef Name='column2' /><Value Type='Choice'>Marley</Value></Eq></And></Where></Query>");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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