Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 1 down vote accepted

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

share|improve this answer
    
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. –  sw6 - KTBFFH Apr 10 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? –  Jared McGrath Apr 10 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. –  sw6 - KTBFFH Apr 10 at 14:45

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/

share|improve this answer
<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/

share|improve this answer

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);
share|improve this answer
    
No I'm actually running my code in developer console for testing purposes as I am using javascript to retrieve list items. –  sw6 - KTBFFH Apr 10 at 3:10

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>");
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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