Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 <Or> nodes?


EDIT: This was my solution to generate the <Or> nodes.

/// Simulates a SQL 'Where In' clause in CAML
/// </summary>
/// <param name="columnType">Specifies the data type for the value contained by the field.</param>
/// <returns>Nested 'Or' elements portion of CAML query</returns>
public static string CamlIn<T>(string internalFieldName, string columnType, T[] values)
{
    XDocument doc = new XDocument();
    XElement prev = null;
    int index = 0;

    while (index < values.Length)
    {
        XElement element =
            new XElement("Or",
                new XElement("Eq",
                    new XElement("FieldRef",
                    new XAttribute("Name", internalFieldName)),
                new XElement("Value",
                    new XAttribute("Type", columnType),
                    values[index++].ToString())));

        if (index == values.Length - 1)
        {
            element.AddFirst(
                new XElement("Eq",
                    new XElement("FieldRef",
                    new XAttribute("Name", internalFieldName)),
                new XElement("Value",
                    new XAttribute("Type", columnType),
                    values[index++].ToString())));
        }

        if (prev != null)
            prev.AddFirst(element);
        else
            doc.Add(element);

        prev = element;
    }

    return doc.ToString(SaveOptions.DisableFormatting);
}

Usage:

int[] ids = new int[] { 1, 2, 4, 5 };
string query = string.Format("<Where>{0}</Where>", CamlIn("SomeColumn", "Number", ids));

Output:

<Where>
    <Or>
        <Or>
            <Or>
                <Eq>
                    <FieldRef Name=\"SomeColumn\" />
                    <Value Type=\"Number\">5</Value>
                </Eq>
                <Eq>
                    <FieldRef Name=\"SomeColumn\" />
                    <Value Type=\"Number\">4</Value>
                </Eq>
            </Or>
            <Eq>
                <FieldRef Name=\"SomeColumn\" />
                <Value Type=\"Number\">2</Value>
            </Eq>
        </Or>
        <Eq>
            <FieldRef Name=\"SomeColumn\" />
            <Value Type=\"Number\">1</Value>
        </Eq>
    </Or>
</Where>

Also made this overload for working with Lookup Fields a bit easier

/// <summary>
/// Simulates a SQL 'Where In' clause in CAML
/// </summary>
/// <param name="lookupId">Specify whether to use the Lookup column's Id or Value.</param>
/// <returns>Nested 'Or' elements portion of CAML query</returns>
public static string CamlIn<T>(string internalFieldName, bool lookupId, T[] values)
{
    XDocument doc = new XDocument();
    XElement prev = null;
    int index = 0;

    while (index < values.Length)
    {
        XElement element =
            new XElement("Or",
                new XElement("Eq",
                    new XElement("FieldRef",
                        new XAttribute("Name", internalFieldName),
                        lookupId ? new XAttribute("LookupId", "TRUE") : null),
                    new XElement("Value",
                        new XAttribute("Type", "Lookup"),
                        values[index++].ToString())));

        if (index == values.Length - 1)
        {
            element.AddFirst(
                new XElement("Eq",
                    new XElement("FieldRef",
                        new XAttribute("Name", internalFieldName),
                        lookupId ? new XAttribute("LookupId", "TRUE") : null),
                    new XElement("Value",
                        new XAttribute("Type", "Lookup"),
                        values[index++].ToString())));
        }

        if (prev != null)
            prev.AddFirst(element);
        else
            doc.Add(element);

        prev = element;
    }

    if (values.Length == 1)
    {
        XElement newRoot = doc.Descendants("Eq").Single();
        doc.RemoveNodes();
        doc.Add(newRoot);
    }

    return doc.ToString(SaveOptions.DisableFormatting);
}
like image 865
Chris Avatar asked Dec 11 '09 22:12

Chris


People also ask

What is CAML Query?

The Query schema of Collaborative Application Markup Language (CAML) is used in various ways within the context of Microsoft SharePoint Foundation to define queries against list data.

How do you write a CAML Query?

CamlQuery camlQuery = new CamlQuery(); camlQuery. ViewXml = "<View><Query><Where><Eq><FieldRef Name='" + fieldInternalName + "'/><Value Type='Text'></Eq>" + fieldValue + "</Value></Eq></Where></Query></View>"; var itemColl = oList. GetItems(camlQuery); ctx. Load(itemColl); ctx.

What is CAML code?

Applies to: SharePoint 2016 | SharePoint Foundation 2013 | SharePoint Online | SharePoint Server 2013. Collaborative Application Markup Language (CAML) is an XML-based language that is used in Microsoft SharePoint Foundation to define the fields and views that are used in sites and lists.

Is CAML Query case-sensitive?

Remarks. Remember that Collaborative Application Markup Language (CAML) is case-sensitive; note the lowercase "t" in the Lt element name.


2 Answers

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>",
                                         "<FieldRef Name='ID' />",
                                           "<Values>",
                                             "<Value Type='Number'>6</Value>",
                                             "<Value Type='Number'>7</Value>",
                                             "<Value Type='Number'>8</Value>",
                                           "</Values>",
                                       "</In>",
                                     "</Where>");
like image 138
ScottE Avatar answered Oct 05 '22 22:10

ScottE


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.

like image 35
Rubens Farias Avatar answered Oct 06 '22 00:10

Rubens Farias