Quantified Expressions (XQuery)
Applies to: SQL Server (all supported versions)
Existential and universal quantifiers specify different semantics for Boolean operators that are applied to two sequences. This is shown in the following table.
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.
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
These are the limitations:
- Type assertion is not supported as part of binding the variable in the quantified expressions.