General XQuery Use Cases
Applies to: 
SQL Server
This topic provides general examples of XQuery use.
Examples
A. Query catalog descriptions to find products and weights
The following query returns the product model IDs and weights, if they exist, from the product catalog description. The query constructs XML that has the following form:
<Product ProductModelID="...">
<Weight>...</Weight>
</Product>
This is the query:
SELECT CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product ProductModelID="{ (/p1:ProductDescription/@ProductModelID)[1] }">
{
/p1:ProductDescription/p1:Specifications/Weight
}
</Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription is not null
Note the following from the previous query:
The namespace keyword in the XQuery prolog defines a namespace prefix that is used in the query body.
The query body constructs the required XML.
In the WHERE clause, the exist() method is used to find only rows that contain product catalog descriptions. That is, the XML that contains the <
ProductDescription> element.
This is the result:
<Product ProductModelID="19"/>
<Product ProductModelID="23"/>
<Product ProductModelID="25"/>
<Product ProductModelID="28"><Weight>Varies with size.</Weight></Product>
<Product ProductModelID="34"/>
<Product ProductModelID="35"/>
The following query retrieves the same information, but only for those product models whose catalog description includes the weight, the <Weight> element, in the specifications, the <Specifications> element. This example uses WITH XMLNAMESPACES to declare the pd prefix and its namespace binding. In this way, the binding is not described in both the query() method and in the exist() method.
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT CatalogDescription.query('
<Product ProductModelID="{ (/pd:ProductDescription/@ProductModelID)[1] }">
{
/pd:ProductDescription/pd:Specifications/Weight
}
</Product>
') as x
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Specifications//Weight ') = 1
In the previous query, the exist() method of the xml data type in the WHERE clause checks to see if there is a <Weight> element in the <Specifications> element.
B. Find product model IDs for product models whose catalog descriptions include front-angle and small size pictures
The XML product catalog description includes the product pictures, the <Picture> element. Each picture has several properties. These include the picture angle, the <Angle> element, and the size, the <Size> element.
For product models whose catalog descriptions include front-angle and small-size pictures, the query constructs XML that has the following form:
< Product ProductModelID="...">
<Picture>
<Angle>front</Angle>
<Size>small</Size>
</Picture>
</Product>
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{ (/pd:ProductDescription/@ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]', 'varchar(20)') = 'front'
AND CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]', 'varchar(20)') = 'small'
Note the following from the previous query:
In the WHERE clause, the exist() method is used to retrieve only rows that have product catalog descriptions with the <
Picture> element.The WHERE clause uses the value() method two times to compare the values of the <
Size> and <Angle> elements.
This is a partial result:
<p1:Product
xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"
ProductModelID="19">
<Picture>
<p1:Angle>front</p1:Angle>
<p1:Size>small</p1:Size>
</Picture>
</p1:Product>
...
C. Create a flat list of the product model name and feature pairs, with each pair enclosed in the <Features> element
In the product model catalog description, the XML includes several product features. All these features are included in the <Features> element. The query uses XML Construction (XQuery) to construct the required XML. The expression in the curly braces is replaced by the result.
SELECT CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $pd in /p1:ProductDescription,
$f in $pd/p1:Features/*
return
<Feature>
<ProductModelName> { data($pd/@ProductModelName) } </ProductModelName>
{ $f }
</Feature>
') as x
FROM Production.ProductModel
WHERE ProductModelID=19
Note the following from the previous query:
$pd/p1:Features/* returns only the element node children of <
Features>, but $pd/p1:Features/node() returns all the nodes. This includes the element nodes, text nodes, processing instructions, and comments.The two FOR loops generate a Cartesian product from which the product name and the individual feature are returned.
The ProductName is an attribute. The XML construction in this query returns it as an element.
This is a partial result:
<Feature>
<ProductModelName>Mountain 100</ProductModelName>
<ProductModelID>19</ProductModelID>
<p1:Warranty
xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
<p1:WarrantyPeriod>3 year</p1:WarrantyPeriod>
<p1:Description>parts and labor</p1:Description>
</p1:Warranty>
</Feature>
<Feature>
<ProductModelName>Mountain 100</ProductModelName>
<ProductModelID>19</ProductModelID>
<p2:Maintenance xmlns:p2="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
<p2:NoOfYears>10</p2:NoOfYears>
<p2:Description>maintenance contact available through your dealer
or any AdventureWorks retail store.</p2:Description>
</p2:Maintenance>
</Feature>
...
...
D. From the catalog description of a product model, list the product model name, model ID, and features grouped inside a <Product> element
Using the information stored in the catalog description of the product model, the following query lists the product model name, model ID, and features grouped inside a <Product> element.
SELECT ProductModelID, CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product>
<ProductModelName>
{ data(/pd:ProductDescription/@ProductModelName) }
</ProductModelName>
<ProductModelID>
{ data(/pd:ProductDescription/@ProductModelID) }
</ProductModelID>
{ /pd:ProductDescription/pd:Features/* }
</Product>
') as x
FROM Production.ProductModel
WHERE ProductModelID=19
This is a partial result:
<Product>
<ProductModelName>Mountain 100</ProductModelName>
<ProductModelID>19</ProductModelID>
<p1:Warranty>... </p1:Warranty>
<p2:Maintenance>... </p2:Maintenance>
<p3:wheel xmlns:p3="https://www.adventure-works.com/schemas/OtherFeatures">High performance wheels.</p3:wheel>
<p4:saddle xmlns:p4="https://www.adventure-works.com/schemas/OtherFeatures">
<p5:i xmlns:p5="http://www.w3.org/1999/xhtml">Anatomic design</p5:i> and made from durable leather for a full-day of riding in comfort.</p4:saddle>
<p6:pedal xmlns:p6="https://www.adventure-works.com/schemas/OtherFeatures">
<p7:b xmlns:p7="http://www.w3.org/1999/xhtml">Top-of-the-line</p7:b> clipless pedals with adjustable tension.</p6:pedal>
...
E. Retrieve product model feature descriptions
The following query constructs XML that includes a <Product> element that has ProductModelID, ProductModelName attributes, and the first two product features. Specifically, the first two product features are the first two child elements of the <Features> element. If there are more features, it returns an empty <There-is-more/> element.
SELECT CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product>
{ /pd:ProductDescription/@ProductModelID }
{ /pd:ProductDescription/@ProductModelName }
{
for $f in /pd:ProductDescription/pd:Features/*[position()<=2]
return
$f
}
{
if (count(/pd:ProductDescription/pd:Features/*) > 2)
then <there-is-more/>
else ()
}
</Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription is not NULL
Note the following from the previous query:
- The FOR ... RETURN loop structure retrieves the first two product features. The position() function is used to find the position of the elements in the sequence.
F. Find element names from the product catalog description that end with "ons"
The following query searches the catalog descriptions and returns all the elements in the <ProductDescription> element whose name ends with "ons".
SELECT ProductModelID, CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $pd in /p1:ProductDescription/*[substring(local-name(.),string-length(local-name(.))-2,3)="ons"]
return
<Root>
{ $pd }
</Root>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription is not NULL
This is a partial result:
ProductModelID Result
-----------------------------------------
19 <Root>
<p1:Specifications xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
...
</p1:Specifications>
</Root>
G. Find summary descriptions that contain the word "Aerodynamic"
The following query retrieves product models whose catalog descriptions contain the word "Aerodynamic" in the summary description:
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT ProductModelID, CatalogDescription.query('
<Prod >
{ /pd:ProductDescription/@ProductModelID }
{ /pd:ProductDescription/pd:Summary }
</Prod>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.value('
contains( string( (/pd:ProductDescription/pd:Summary)[1] ),"Aerodynamic")','bit') = 1
Note that the SELECT query specifies query() and value() methods of the xml data type. Therefore, instead of repeating the namespaces declaration two times in two difference query prologs, the prefix pd is used in the query and is defined only once by using WITH XMLNAMESPACES.
Note the following from the previous query:
The WHERE clause is used to retrieve only the rows where the catalog description contains the word "Aerodynamic" in the <
Summary> element.The contains() function is used to see if the word is included in the text.
The value() method of the xml data type compares the value returned by contains() to 1.
This is the result:
ProductModelID Result
-------------- ------------------------------------------
28 <Prod ProductModelID="28">
<pd:Summary xmlns:pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
<p1:p xmlns:p1="http://www.w3.org/1999/xhtml">
A TRUE multi-sport bike that offers streamlined riding and a
revolutionary design. Aerodynamic design lets you ride with the
pros, and the gearing will conquer hilly roads.</p1:p>
</pd:Summary>
</Prod>
H. Find product models whose catalog descriptions do not include product model pictures
The following query retrieves ProductModelIDs for product models whose catalog descriptions do no include a <Picture> element.
SELECT ProductModelID
FROM Production.ProductModel
WHERE CatalogDescription is not NULL
AND CatalogDescription.exist('declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
/p1:ProductDescription/p1:Picture
') = 0
Note the following from the previous query:
If the exist() method in the WHERE clause returns False (0), the product model ID is returned. Otherwise, it is not returned.
Because all the product descriptions include a <
Picture> element, the result set is empty in this case.
See Also
XQueries Involving Hierarchy
XQueries Involving Order
XQueries Handling Relational Data
String Search in XQuery
Handling Namespaces in XQuery
Add Namespaces to Queries with WITH XMLNAMESPACES
XML Data (SQL Server)
XQuery Language Reference (SQL Server)