nodes() Method (xml Data Type)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
Every xml data type instance has an implicitly provided context node. For the XML instance stored in a column or variable, this node is the document node. The document node is the implicit node at the top of every xml data type instance.
The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes that is identified with the query expression. This way, later queries can navigate relative to these context nodes.
You can retrieve multiple values from the rowset. For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. The value() method, when applied to the XML instance, returns only one value.
Syntax
nodes (XQuery) as Table(Column)
Arguments
XQuery
Is a string literal, an XQuery expression. If the query expression constructs nodes, these constructed nodes are exposed in the resulting rowset. If the query expression results in an empty sequence, the rowset is empty as well. If the query expression statically results in a sequence that contains atomic values instead of nodes, a static error is raised.
Table(Column)
Is the table name and the column name for the resulting rowset.
Remarks
As an example, assume that you have the following table:
T (ProductModelID INT, Instructions XML)
The following manufacturing instructions document is stored in the table. Only a fragment is shown. Notice that there are three manufacturing locations in the document.
<root>
<Location LocationID="10"...>
<step>...</step>
<step>...</step>
...
</Location>
<Location LocationID="20" ...>
...
</Location>
<Location LocationID="30" ...>
...
</Location>
</root>
A nodes()
method invocation with the query expression /root/Location
would return a rowset with three rows, each containing a logical copy of the original XML document, and with the context item set to one of the <Location>
nodes:
Product
ModelID Instructions
----------------------------------
1 <root><Location LocationID="10" ... />
<Location LocationID="20" ... />
<Location LocationID="30" .../></root>
1 <root><Location LocationID="10" ... />
<Location LocationID="20" ... />
<Location LocationID="30" .../></root>
1 <root><Location LocationID="10" ... />
<Location LocationID="20" ... />
<Location LocationID="30" .../></root>
You can then query this rowset by using xml data type methods. The following query extracts the subtree of the context item for each generated row:
SELECT T2.Loc.query('.')
FROM T
CROSS APPLY Instructions.nodes('/root/Location') AS T2(Loc)
Here is the result:
ProductModelID Instructions
----------------------------------
1 <Location LocationID="10" ... />
1 <Location LocationID="20" ... />
1 <Location LocationID="30" .../>
The rowset returned has maintained the type information. You can apply xml data type methods, such as query(), value(), exist(), and nodes(), to the result of a nodes() method. However, you can't apply the modify() method to modify the XML instance.
Also, the context node in the rowset can't be materialized. That is, you can't use it in a SELECT statement. However, you can use it in IS NULL and COUNT(*).
Scenarios for using the nodes() method are the same as for using OPENXML (Transact-SQL), which provides a rowset view of the XML. However, you don't have to use cursors when you use the nodes() method on a table that contains several rows of XML documents.
The rowset returned by the nodes() method is an unnamed rowset. So, it must be explicitly named by using aliasing.
The nodes() function can't be applied directly to the results of a user-defined function. To use the nodes() function with the result of a scalar user-defined function, you can either:
- Assign the result of the user-defined function to a variable
- Use a derived table to assign a column alias to the user-defined function return value and then use
CROSS APPLY
to select from the alias.
The following example shows one way to use CROSS APPLY
to select from the result of a user-defined function.
USE AdventureWorks;
GO
CREATE FUNCTION XTest()
RETURNS XML
AS
BEGIN
RETURN '<document/>';
END;
GO
SELECT A2.B.query('.')
FROM
(SELECT dbo.XTest()) AS A1(X)
CROSS APPLY X.nodes('.') A2(B);
GO
DROP FUNCTION XTest;
GO
Examples
Using nodes() method against a variable of xml type
In the following example, there's an XML document that has a <Root
> top-level element and three <row
> child elements. The query uses the nodes()
method to set separate context nodes, one for each <row
> element. The nodes()
method returns a rowset with three rows. Each row has a logical copy of the original XML, with each context node identifying a different <row
> element in the original document.
The query then returns the context node from each row:
DECLARE @x XML
SET @x='<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
</Root>'
SELECT T.c.query('.') AS result
FROM @x.nodes('/Root/row') T(c)
GO
In the following example result, the query method returns the context item and its content:
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3"/>
Applying the parent accessor on the context nodes returns the <Root
> element for all three:
SELECT T.c.query('..') AS result
FROM @x.nodes('/Root/row') T(c)
GO
Here is the result:
<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
</Root>
<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
</Root>
<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
</Root>
Specifying the nodes() method against a column of xml type
The bicycle manufacturing instructions are used in this example and are stored in the Instructions xml type column of the ProductModel table.
In the following example, the nodes()
method is specified against the Instructions
column of xml type in the ProductModel
table.
The nodes()
method sets the <Location
> elements as context nodes by specifying the /MI:root/MI:Location
path. The resulting rowset includes logical copies of the original document, one for each <Location
> node in the document, with the context node set to the <Location
> element. As a result, the nodes()
function gives a set of <Location
> context nodes.
The query()
method against this rowset requests self::node
and returns the <Location>
element in each row.
In this example, the query sets each <Location
> element as a context node in the manufacturing instructions document of a specific product model. You can use these context nodes to retrieve values such as these:
Find Location IDs in each <
Location
>Retrieve manufacturing steps (<
step
> child elements) in each <Location
>
This query returns the context item, in which the abbreviated syntax '.'
for self::node()
is specified, in the query()
method.
Note the following:
The
nodes()
method is applied to the Instructions column and returns a rowset,T (C)
. This rowset contains logical copies of the original manufacturing instructions document with/root/Location
as the context item.CROSS APPLY applies
nodes()
to each row in theProductModel
table and returns only the rows that produce a result set.SELECT C.query('.') as result FROM Production.ProductModel CROSS APPLY Instructions.nodes(' declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; /MI:root/MI:Location') as T(C) WHERE ProductModelID=7
Here is the partial result:
<MI:Location LocationID="10" ...> <MI:step ... /> ... </MI:Location> <MI:Location LocationID="20" ... > <MI:step ... /> ... </MI:Location> ...
Applying nodes() to the rowset returned by another nodes() method
The following code queries the XML documents for the manufacturing instructions in the Instructions
column of ProductModel
table. The query returns a rowset that contains the product model ID, manufacturing locations, and manufacturing steps.
Note the following:
The
nodes()
method is applied to theInstructions
column and returns theT1 (Locations)
rowset. This rowset contains logical copies of the original manufacturing instructions document, with/root/Location
element as the item context.nodes()
is applied to theT1 (Locations)
rowset and returns theT2 (steps)
rowset. This rowset contains logical copies of the original manufacturing instructions document, with/root/Location/step
element as the item context.
SELECT ProductModelID, Locations.value('./@LocationID','int') AS LocID,
steps.query('.') AS Step
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') AS T1(Locations)
CROSS APPLY T1.Locations.nodes('
declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
./MI:step ') AS T2(steps)
WHERE ProductModelID=7
GO
Here is the result:
ProductModelID LocID Step
----------------------------
7 10 <step ... />
7 10 <step ... />
...
7 20 <step ... />
7 20 <step ... />
7 20 <step ... />
...
The query declares the MI
prefix two times. Instead, you can use WITH XMLNAMESPACES
to declare the prefix one time and use it in the query:
WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS MI)
SELECT ProductModelID, Locations.value('./@LocationID','int') AS LocID,
steps.query('.') AS Step
FROM Production.ProductModel
CROSS APPLY Instructions.nodes('
/MI:root/MI:Location') AS T1(Locations)
CROSS APPLY T1.Locations.nodes('
./MI:step ') as T2(steps)
WHERE ProductModelID=7
GO
See Also
Add Namespaces to Queries with WITH XMLNAMESPACES
Create Instances of XML Data
xml Data Type Methods