Share via

  • Article

Specifying Axis in a Path Expression Step

An axis step in a path expression includes the following components:

For more information, see Path Expressions (XQuery).

The XQuery implementation in SQL Server supports the following axis steps,

Axis

Description

child

Returns children of the context node.

descendant

Returns all descendants of the context node.

parent

Returns the parent of the context node.

attribute

Returns attributes of the context node.

self

Returns the context node itself.

descendant-or-self

Returns the context node and all descendants of the context node.

All these axes, except the parent axis, are forward axes. The parent axis is a reverse axis, because it searches backward in the document hierarchy. For example, the relative path expression child::ProductDescription/child::Summary has two steps, and each step specifies a child axis. The first step retrieves the <ProductDescription> element children of the context node. For each <ProductDescription> element node, the second step retrieves the <Summary> element node children.

The relative path expression, child::root/child::Location/attribute::LocationID, has three steps. The first two steps each specify a child axis, and the third step specifies the attribute axis. When executed against the manufacturing instructions XML documents in the Production.ProductModel table, the expression returns the LocationID attribute of the <Location> element node child of the <root> element.

Examples

The query examples in this topic are specified against xml type columns in the AdventureWorks2008R2 database. For information about these columns, see xml Data Type Representation in the AdventureWorks2008R2 Database.

A. Specifying a child axis

For a specific product model, the following query retrieves the <Features> element node children of the <ProductDescription> element node from the product catalog description stored in the Production.ProductModel table.

SELECT CatalogDescription.query('
declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /child::PD:ProductDescription/child::PD:Features')
FROM Production.ProductModel
WHERE ProductModelID=19

Note the following from the previous query:

  • The query() method of the xml data type specifies the path expression.

  • Both steps in the path expression specify a child axis and the node names, ProductDescription and Features, as node tests. For information about node tests, see Specifying Node Test in a Path Expression Step.

B. Specifying descendant and descendant-or-self axes

The following example uses descendant and descendant-or-self axes. The query in this example is specified against an xml type variable. The XML instance is simplified in order to easily illustrate the difference in the generated results.

declare @x xml
set @x='
<a>
 <b>text1
   <c>text2
     <d>text3</d>
   </c>
 </b>
</a>'
declare @y xml
set @y = @x.query('
  /child::a/child::b
')
select @y

In the following result, the expression returns the <b> element node child of the <a> element node:

<b>text1
   <c>text2
     <d>text3</d>
   </c>
</b>

In this expression, if you specify a descendant axis for the path expression,

/child::a/child::b/descendant::*, you are asking for all descendants of the <b> element node.

The asterisk (*) in the node test represents the node name as a node test. Therefore, the primary node type of the descendant axis, the element node, determines the types of nodes returned. That is, the expression returns all the element nodes.. Text nodes are not returned. For more information about the primary node type and its relationship with the node test, see Specifying Node Test in a Path Expression Step topic.

The element nodes <c> and <d> are returned, as shown in the following result:

<c>text2
     <d>text3</d>
</c>
<d>text3</d>

If you specify a descendant-or-self axis instead of the descendant axis, /child::a/child::b/descendant-or-self::* returns the context node, element <b>, and its descendant.

This is the result:

<b>text1
   <c>text2
     <d>text3</d>
   </c>
</b>

<c>text2
     <d>text3</d>
</c>

<d>text3</d>

The following sample query against the AdventureWorks2008R2 database retrieves all the descendant element nodes of the <Features> element child of the <ProductDescription> element:

SELECT CatalogDescription.query('
declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  /child::PD:ProductDescription/child::PD:Features/descendant::*
')
FROM  Production.ProductModel
WHERE ProductModelID=19

C. Specifying a parent axis

The following query returns the <Summary> element child of the <ProductDescription> element in the product catalog XML document stored in the Production.ProductModel table.

This example uses the parent axis to return to the parent of the <Feature> element and retrieve the <Summary> element child of the <ProductDescription> element.

SELECT CatalogDescription.query('
declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
  
/child::PD:ProductDescription/child::PD:Features/parent::PD:ProductDescription/child::PD:Summary
')
FROM   Production.ProductModel
WHERE  ProductModelID=19

In this query example, the path expression uses the parent axis. You can rewrite the expression without the parent axis, as shown in the following:

  /child::PD:ProductDescription[child::PD:Features]/child::PD:Summary

A more useful example of the parent axis is provided in the following example.

Each product model catalog description stored in the CatalogDescription column of the ProductModel table has a <ProductDescription> element that has the ProductModelID attribute and <Features> child element, as shown in the following fragment:

<ProductDescription ProductModelID="..." >
  ...
  <Features>
    <Feature1>...</Feature1>
    <Feature2>...</Feature2>
   ...
</ProductDescription>

The query sets an iterator variable, $f, in the FLWOR statement to return the element children of the <Features> element. For more information, see FLWOR Statement and Iteration (XQuery). For each feature, the return clause constructs an XML in the following form:

<Feature ProductModelID="...">...</Feature>
<Feature ProductModelID="...">...</Feature>

To add the ProductModelID for each <Feature> element, the parent axis is specified:

SELECT CatalogDescription.query('
declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
  for $f in /child::PD:ProductDescription/child::PD:Features/child::*
  return
   <Feature
     ProductModelID="{ ($f/parent::PD:Features/parent::PD:ProductDescription/attribute::ProductModelID)[1]}" >
          { $f }
   </Feature>
')
FROM  Production.ProductModel
WHERE ProductModelID=19

This is the partial result:

<Feature ProductModelID="19">
  <wm:Warranty 
   xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
    <wm:WarrantyPeriod>3 years</wm:WarrantyPeriod>
    <wm:Description>parts and labor</wm:Description>
  </wm:Warranty>
</Feature>
<Feature ProductModelID="19">
  <wm:Maintenance 
   xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
    <wm:NoOfYears>10 years</wm:NoOfYears>
    <wm:Description>maintenance contract available through your dealer 
                  or any Adventure Works retail store.</wm:Description>
  </wm:Maintenance>
</Feature>
<Feature ProductModelID="19">
  <p1:wheel 
   xmlns:p1="https://www.adventure-works.com/schemas/OtherFeatures">
      High performance wheels.
  </p1:wheel>
</Feature>

Note that the predicate [1] in the path expression is added to ensure that a singleton value is returned.