And Element (Query)

MSDN Library
Web Development
SharePoint Products and Technologies
Windows SharePoint Services 3.0
SDK Documentation
General Reference
Reference
Collaborative Application Markup Language Core Schemas
Query Schema

And Element (Query)

And Element (Query)

Used within the Where element to group filters in a query for a view.

<And>
</And>

Attributes

Attribute	Description
None	N/A

Child Elements

And, BeginsWith, Contains, DateRangesOverlap, Eq, Geq, Gt, IsNotNull, IsNull, Leq, Lt, Membership, Neq, Or

Parent Elements

And, Or, Where

Occurrences

Minimum: 0

Maximum: Unbounded

Remarks

This element can be nested inside other And and Or elements. The server supports unlimited complicated queries.

Example

The following example conveys criteria for a query on the ProductID field: (ProductID = J1539 AND ProductID = J9862) AND (ProductID = J0394 OR ProductID = J4589).

Xml

<And>
  <And>
    <Eq>
      <FieldRef Name="ProductID"/>
      <Value Type="Text">J1539</Value>
    </Eq>
    <Eq>
      <FieldRef Name="ProductID"/>
      <Value Type="Text">J9862</Value>
    </Eq>
  </And>
  <Or>
    <Eq>
      <FieldRef Name="ProductID"/>
      <Value Type="Text">J0394</Value>
    </Eq>
    <Eq>
      <FieldRef Name="ProductID"/>
      <Value Type="Text">J4589</Value>
    </Eq>
  </Or>
</And>

The following example performs a query for cases in which values of the Status field do not equal Completed and values of the Sent field are null. The records returned are sorted in descending order according to values of the Modified field.

Xml

<Query>
  <OrderBy>
    <FieldRef Name="Modified" Ascending="FALSE"></FieldRef>
  </OrderBy>
  <Where>
    <And>
      <Neq>
        <FieldRef Name="Status"></FieldRef>
        <Value Type="Text">Completed</Value>
      </Neq>
      <IsNull>
        <FieldRef Name="Sent"></FieldRef>
      </IsNull>
    </And>
  </Where>
</Query>

Community Content

Only compares a pair of "values"

Added by:Steve Butler MSFT

<And> and <Or> can only be used to compare 2 boolean expressions. Doing otherwise results in a runtime error in Sharepoint Services (described by the ever-helpful "Cannot complete this action. Please try again." message).

For example, even though the following makes sense as a SQL construct, it will cause Sharepoint to throw an error:

<And>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">Completed</Value>
</Neq>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">5-Closed</Value>
</Neq>
<Eq>
<FieldRef Name="AssignedTo" LookupId="TRUE" />
<Value Type="int">[CurrentUser]</Value>
</Eq>
<Eq>
<FieldRef Name="IsCurrent" />
<Value Type="Boolean">1</Value>
</Eq>
</And>

In order to correctly execute the CAML must be written comparing pairs of values. For example:

<And>
<And>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">Completed</Value>
</Neq>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">5-Closed</Value>
</Neq>
</And>
<And>
<Eq>
<FieldRef Name="AssignedTo" LookupId="TRUE" />
<Value Type="int">[CurrentUser]</Value>
</Eq>
<Eq>
<FieldRef Name="IsCurrent" />
<Value Type="Boolean">1</Value>
</Eq>
</And>
</And>

Grouping Clauses in query

Added by:Moutasem al-awa

<And> and <Or> clauses could be grouped together in SharePoint designer -if you are working with DataView Webparts-, or in the CAML Query itself.
For exmaple, if we have the following Expression

(A = 1 AND B = 2 AND C = 3) OR D = 4 and you want translate it to CAML Query, you need to use the attribure <EXP Group= true>, so the resulting query is :

<Where>
<Or>
<And Group="true">
<And>
<Eq>
<FieldRef Name="A"/>
<Value Type="Text">1</Value>
</Eq>
<Eq>
<FieldRef Name="B"/>
<Value Type="Text">2</Value>
</Eq>
</And>
<Eq>
<FieldRef Name="C"/>
<Value Type="Text">3</Value>
</Eq>
</And>
<Eq>
<FieldRef Name="D"/>
<Value Type="Text">4</Value>
</Eq>
</Or>
</Where>