Quantified Expressions (XQuery)
Applies to: 
SQL Server
Existential and universal quantifiers specify different semantics for Boolean operators that are applied to two sequences. This is shown in the following table.
Existential quantifier
Given two sequences, if any item in the first sequence has a match in the second sequence, based on the comparison operator that is used, the returned value is True.
Universal quantifier
Given two sequences, if every item in the first sequence has a match in the second sequence, the returned value is True.
XQuery supports quantified expressions in the following form:
( some | every ) <variable> in <Expression> (,...) satisfies <Expression>
You can use these expressions in a query to explicitly apply either existential or universal quantification to an expression over one or several sequences. In SQL Server, the expression in the satisfies clause has to result in one of the following: a node sequence, an empty sequence, or a Boolean value. The effective Boolean value of the result of that expression will be used in the quantification. The existential quantification that uses some will return True if at least one of the values bound by the quantifier has a True result in the satisfy expression. The universal quantification that uses every must have True for all values bound by the quantifier.
For example, the following query checks every <Location> element to see whether it has a LocationID attribute.
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
if (every $WC in //AWMI:root/AWMI:Location
satisfies $WC/@LocationID)
then
<Result>All work centers have workcenterLocation ID</Result>
else
<Result>Not all work centers have workcenterLocation ID</Result>
') as Result
FROM Production.ProductModel
where ProductModelID=7
Because LocationID is a required attribute of the <Location> element, you receive the expected result:
<Result>All work centers have Location ID</Result>
Instead of using the query() method, you can use the value() method to return the result to the relational world, as shown in the following query. The query returns True if all work center locations have LocationID attributes. Otherwise, the query returns False.
SELECT Instructions.value('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
every $WC in //AWMI:root/AWMI:Location
satisfies $WC/@LocationID',
'nvarchar(10)') as Result
FROM Production.ProductModel
where ProductModelID=7
The following query checks to see if one of the product pictures is small. In the product catalog XML, various angles are stored for each product picture of a different size. You might want to ensure that each product catalog XML includes at least one small-sized picture. The following query accomplishes this:
SELECT ProductModelID, CatalogDescription.value('
declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
some $F in /PD:ProductDescription/PD:Picture
satisfies $F/PD:Size="small"', 'nvarchar(20)') as SmallPicturesStored
FROM Production.ProductModel
WHERE ProductModelID = 19
This is a partial result:
ProductModelID SmallPicturesStored
-------------- --------------------
19 true
Implementation Limitations
These are the limitations:
- Type assertion is not supported as part of binding the variable in the quantified expressions.