Events
Sep 15, 6 AM - Sep 17, 3 PM
The best SQL community-led learning event. Sept 2025. Save €200 with code FABLEARN.
Get registeredThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to:
SQL Server
Azure SQL Database
The following examples show how axes are specified in XPath queries.
The XPath queries in these examples are specified against the mapping schema contained in SampleSchema1.xml. For information about this sample schema, see Sample Annotated XSD Schema for XPath Examples (SQLXML 4.0).
The following XPath query selects all the <Contact> child elements of the context node:
/child::Contact
In the query, child
is the axis and Contact
is the node test (TRUE if Contact
is an <element> node, because <element> is the primary node type associated with the child
axis).
The child
axis is the default. Therefore, the query can be written as:
/Contact
Copy the sample schema code and paste it into a text file. Save the file as SampleSchema1.xml.
Create the following template (XPathAxesSampleA.xml) and save it in the directory where SampleSchema1.xml was saved.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:xpath-query mapping-schema="SampleSchema1.xml">
/Contact
</sql:xpath-query>
</ROOT>
The directory path specified for the mapping schema (SampleSchema1.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
mapping-schema="C:\MyDir\SampleSchema1.xml"
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
Here is the partial result set of the template execution:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Contact ContactID="1" LastName="Achong" FirstName="Gustavo" Title="Mr." />
<Contact ContactID="2" LastName="Abel" FirstName="Catherine" Title="Ms." />
<Contact ContactID="3" LastName="Abercrombie" FirstName="Kim" Title="Ms." />
<Contact ContactID="4" LastName="Acevedo" FirstName="Humberto" Title="Sr." />
...
</ROOT>
The following XPath query selects all the <Order> element children of the <Customer> element children of the context node:
/child::Customer/child::Order
In the query, child
is the axis and Customer
and Order
are the node tests (these node tests are TRUE if Customer and Order are <element> nodes, because the <element> node is the primary node for the child axis). For each node matching <Customer>, the nodes matching <Orders> are added to the result. Only <Order> is returned in the result set.
The child axis is the default. Therefore, the query can be specified as:
/Customer/Order
Copy the sample schema code and paste it into a text file. Save the file as SampleSchema1.xml.
Create the following template (XPathAxesSampleB.xml) and save it in the directory where:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:xpath-query mapping-schema="SampleSchema1.xml">
/Customer/Order
</sql:xpath-query>
</ROOT>
The directory path specified for the mapping schema (SampleSchema1.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
mapping-schema="C:\MyDir\SampleSchema1.xml"
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
Here is the partial result set of the template execution:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Order SalesOrderID="Ord-43860" SalesPersonID="280"
OrderDate="2001-08-01T00:00:00"
DueDate="2001-08-13T00:00:00"
ShipDate="2001-08-08T00:00:00">
<OrderDetail ProductID="Prod-729" UnitPrice="226.8571"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-732" UnitPrice="440.1742"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-738" UnitPrice="220.2496"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-753" UnitPrice="2576.3544"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-756" UnitPrice="1049.7528"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-758" UnitPrice="1049.7528"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-761" UnitPrice="503.3507"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-762" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-763" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-765" UnitPrice="503.3507"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-768" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-770" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
</Order>
...
</ROOT>
If the XPath query is specified as Customer/Order/OrderDetail
, from each node matching <Customer> the query navigates to its <Order> elements. And for each node matching <Order>, the query adds the nodes <OrderDetail> to the result. Only <OrderDetail> is returned in the result set.
The following query retrieves all the <Order> elements with a parent <Customer> element with a CustomerID attribute value of 1. The query uses the child axis in the predicate to find the parent of the <Order> element.
/child::Customer/child::Order[../@CustomerID="1"]
The child axis is the default axis. Therefore, the query can be specified as:
/Customer/Order[../@CustomerID="1"]
The XPath query is equivalent to:
/Customer[@CustomerID="1"]/Order.
Note
The XPath query /Order[../@CustomerID="1"]
will return an error because there is no parent of <Order>. Although there may be elements in the mapping schema that contain <Order>, the XPath did not begin at any of them; consequently, <Order> is considered to be the top-level element type in the document.
Copy the sample schema code and paste it into a text file. Save the file as SampleSchema1.xml.
Create the following template (XPathAxesSampleC.xml) and save it in the directory where:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:xpath-query mapping-schema="SampleSchema1.xml">
/Customer/Order[../@CustomerID="1"]
</sql:xpath-query>
</ROOT>
The directory path specified for the mapping schema (SampleSchema1.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
mapping-schema="C:\MyDir\SampleSchema1.xml"
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
Here is the partial result set of the template execution:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Order SalesOrderID="Ord-43860" SalesPersonID="280"
OrderDate="2001-08-01T00:00:00"
DueDate="2001-08-13T00:00:00"
ShipDate="2001-08-08T00:00:00">
<OrderDetail ProductID="Prod-729" UnitPrice="226.8571"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-732" UnitPrice="440.1742"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-738" UnitPrice="220.2496"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-753" UnitPrice="2576.3544"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-756" UnitPrice="1049.7528"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-758" UnitPrice="1049.7528"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-761" UnitPrice="503.3507"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-762" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-763" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-765" UnitPrice="503.3507"
OrderQty="2" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-768" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-770" UnitPrice="503.3507"
OrderQty="1" UnitPriceDiscount="0" />
</Order>
...
</Order>
</ROOT>
The following XPath query selects all the <Customer> child elements of the context node with a CustomerID attribute value of 1:
/child::Customer[attribute::CustomerID="1"]
In the predicate attribute::CustomerID
, attribute
is the axis and CustomerID
is the node test (if CustomerID
is an attribute the node test is TRUE, because the <attribute> node is the primary node for the attribute
axis).
A shortcut to the attribute
axis (@) can be specified, and because child
is the default axis, it can be omitted from the query:
/Customer[@CustomerID="1"]
Copy the sample schema code and paste it into a text file. Save the file as SampleSchema1.xml.
Create the following template (XPathAxesSampleD.xml) and save it in the directory where SampleSchema1.xml is saved.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:xpath-query mapping-schema="SampleSchema1.xml">
child::Customer[attribute::CustomerID="1"]
</sql:xpath-query>
</ROOT>
The directory path specified for the mapping schema (SampleSchema1.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
mapping-schema="C:\MyDir\SampleSchema1.xml"
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
Here is the partial result set of the template execution:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Customer CustomerID="1" SalesPersonID="280"
TerritoryID="1" AccountNumber="1"
CustomerType="S" Orders="Ord-43860 Ord-44501 Ord-45283 Ord-46042">
<Order SalesOrderID="Ord-43860" SalesPersonID="280"
OrderDate="2001-08-01T00:00:00"
DueDate="2001-08-13T00:00:00"
ShipDate="2001-08-08T00:00:00">
<OrderDetail ProductID="Prod-729" UnitPrice="226.8571"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-732" UnitPrice="440.1742"
OrderQty="1" UnitPriceDiscount="0" />
<OrderDetail ProductID="Prod-738" UnitPrice="220.2496"
OrderQty="1" UnitPriceDiscount="0" />
...
</Order>
...
</Customer>
</ROOT>
Events
Sep 15, 6 AM - Sep 17, 3 PM
The best SQL community-led learning event. Sept 2025. Save €200 with code FABLEARN.
Get registeredAsk Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in