Sql In Equivalent In Caml

Is there a 'nice' way to create a CAML query for SharePoint that does something like this? SELECT * FROM table WHERE Id IN (3, 12, ...) Or am I stuck with a nightmare of nested &l

Solution 1:

For those using Sharepoint 2010, there is an IN element available:

http://msdn.microsoft.com/en-us/library/ff625761.aspx

Here's a working example:

SPQuery locationsQuery = new SPQuery();
locationsQuery.Query = string.Concat("<Where>",
                                       "<In>",
                                         "<FieldRefName='ID' />",
                                           "<Values>",
                                             "<ValueType='Number'>6</Value>",
                                             "<ValueType='Number'>7</Value>",
                                             "<ValueType='Number'>8</Value>",
                                           "</Values>",
                                       "</In>",
                                     "</Where>");

Solution 2:

NO, you'll need to deal with nested OR tags; these are supported query instructions on CAML

Maybe CAML.NET can help you in your quest.

Solution 3:

FullTextSqlQuery

It is possible to search MOSS using SQL statements, using the FullTextSqlQuery class. I have no experience of using this class personally. These articles may be of use:

• http://blogit.create.pt/blogs/ricardocosta/archive/2007/06/15/How-to-use-FullTextSqlQuery-to-search-in-WSS.aspx
• http://www.dotnetmafia.com/blogs/dotnettipoftheday/archive/2008/03/06/how-to-use-the-moss-enterprise-search-fulltextsqlquery-class.aspx

YACAMLQT

Alternatively, there is also YACAMLQT (Yet Another CAML Query Tool) which allows you to create SharePoint CAML queries using a T-SQL syntax.

LINQ to SharePoint

If you are up to speed with LINQ, then the LINQ to SharePoint project provides a tool to query SharePoint lists using the LINQ syntax. Please note, this tool is still in the alpha testing phase, so it may not be production ready.

U2U CAML Query Builder

If you are working with CAML queries, I would recommend using the U2U CAML Query Builder for SharePoint (2003 and 2007) tool to build up your CAML queries. The tool allows you to build up your query string, and to execute it against the target list, using a point-and-click interface, as shown below.

_{(source: u2u.net)}

Of the above four methods, I can recommend the U2U CAML Query Builder, having used it almost daily over the last 6 months. It also appears to be the most widely used CAML tool in the SharePoint community .

Note, if you are building the CAML queries in code, then I recommend that you take a look at the CAML.NET project on CodePlex, which provides "a set of .NET language-based tools for creating dynamic, reusable CAML query components".

Solution 4:

I faced a similar thing and ultimately had to create a recursive algorithm to generate the nested OR structure. Here is my algorithm

var DynamicQuery = '<Query><Where>{{DYNAMICSTRING}}</Where></Query>';
var OneOR = '<Or><Eq><FieldRefName="IMEI" /><ValueType="Text">{{SearchValue}}</Value></Eq>{{DYNAMICSTRING}}</Or>';
var TwoOr = '<Or><Eq><FieldRefName="IMEI" /><ValueType="Text">{{SearchValue}}</Value></Eq><Eq><FieldRefName="IMEI" /><ValueType="Text">{{SearchValue}}</Value></Eq></Or>';
var OnlyEq =  '<Eq><FieldRefName="IMEI" /><ValueType="Text">{{SearchValue}}</Value></Eq>';
function generateAdvancedInQuery(x){
    if(x.length == 1)
        return OnlyEq.replace('{{SearchValue}}',x[0]);
    else if(x.length == 2)
        return TwoOr.replace('{{SearchValue}}',x[0]).replace('{{SearchValue}}',x[1]);
    else 
        return OneOR.replace('{{SearchValue}}',x[x.length-1]).replace('{{DYNAMICSTRING}}',generateAdvancedInQuery(x.splice(0,x.length-1) ) );   
}

x = ['438753234098792','438753234098793','438753234098794','438753234098795','438753234098796','438753234098797','438753234098798'];

var Caml = DynamicQuery.replace('{{DYNAMICSTRING}}',generateAdvancedInQuery(x) )

This generates the XML caml query as

<Query><Where><Or><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098798</Value></Eq><Or><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098797</Value></Eq><Or><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098796</Value></Eq><Or><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098795</Value></Eq><Or><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098794</Value></Eq><Or><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098792</Value></Eq><Eq><FieldRefName="IMEI" /><ValueType="Text">438753234098793</Value></Eq></Or></Or></Or></Or></Or></Or></Where></Query>