I am working on proof-of-concept code to dynamically generate CAML based on keywords provided to a highly-specific search web service that I am writing. I am not using the SharePoint-provided search web service for this proof. I have done so already for what I am trying to achieve. From all of my research, I cannot find a close example for what I am trying to achieve, which is to check multiple fields for multiple values. Yes, I have looked on SO already for my answer, including this one: Need help on building CAML Query.
With that said, if it is possible, how can the following SQL-like query be written in CAML?
SELECT FirstName, LastName, Description, Profile
FROM SomeFakeTable
WHERE (FirstName = 'John' OR LastName = 'John' OR Description = 'John' OR Profile='John')
AND (FirstName = 'Doe' OR LastName = 'Doe' OR Description = 'Doe' OR Profile='Doe')
AND (FirstName = '123' OR LastName = '123' OR Description = '123' OR Profile='123')
Since you are not allowed to put more than two conditions in one condition group (And | Or) you have to create an extra nested group (MSDN). The expression A AND B AND C
looks like this:
<And>
A
<And>
B
C
</And>
</And>
Your SQL like sample translated to CAML (hopefully with matching XML tags ;) ):
<Where>
<And>
<Or>
<Eq>
<FieldRef Name='FirstName' />
<Value Type='Text'>John</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>John</Value>
</Eq>
<Eq>
<FieldRef Name='Profile' />
<Value Type='Text'>John</Value>
</Eq>
</Or>
</Or>
<And>
<Or>
<Eq>
<FieldRef Name='FirstName' />
<Value Type='Text'>Doe</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>Doe</Value>
</Eq>
<Eq>
<FieldRef Name='Profile' />
<Value Type='Text'>Doe</Value>
</Eq>
</Or>
</Or>
<Or>
<Eq>
<FieldRef Name='FirstName' />
<Value Type='Text'>123</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name='LastName' />
<Value Type='Text'>123</Value>
</Eq>
<Eq>
<FieldRef Name='Profile' />
<Value Type='Text'>123</Value>
</Eq>
</Or>
</Or>
</And>
</And>
</Where>