General XQuery Use Cases

Article
04/03/2023

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.

Share via

General XQuery Use Cases

Examples

A. Query catalog descriptions to find products and weights

B. Find product model IDs for product models whose catalog descriptions include front-angle and small size pictures

C. Create a flat list of the product model name and feature pairs, with each pair enclosed in the <Features> element

D. From the catalog description of a product model, list the product model name, model ID, and features grouped inside a <Product> element

E. Retrieve product model feature descriptions

F. Find element names from the product catalog description that end with "ons"

G. Find summary descriptions that contain the word "Aerodynamic"

H. Find product models whose catalog descriptions do not include product model pictures

See Also

Feedback

Additional resources