nodes() Method (xml Data Type)

nodes() Method (xml Data Type)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

nodes() Method (xml Data Type)

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 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 identified with the query expression, so that subsequent 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. Note that the value() method, when applied to the XML instance, returns only one value.

This is the general syntax:

nodes (XQuery) as Table(Column)

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 will be empty. 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.

For 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. Note 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="20" ... />
             <Location LocationID="30" .../></root>
1      <root><Location LocationID="10" ... />
             
             <Location LocationID="30" .../></root>
1      <root><Location LocationID="10" ... />
             <Location LocationID="20" ... />
             </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)

This is the result:

ProductModelID  Instructions
----------------------------------
1        <Location LocationID="10" ... />
1        <Location LocationID="20" ... />
1        <Location LocationID="30" .../>

Remarks

Note that 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 cannot apply the modify() method to modify the XML instance.

Also, the context node in the rowset cannot be materialized. That is, you cannot 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. This provides a rowset view of the XML. However, you do not have to use cursors when you use the nodes() method on a table that contains several rows of XML documents.

Note that the rowset returned by the nodes() method is an unnamed rowset. Therefore, it must be explicitly named by using aliasing.

The nodes() function cannot 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 or 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

A. Using nodes() method against a variable of xml type

In the following example, there is 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

The following is the result. In this example, 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

This 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>

The following query specifies an absolute path. A query on a context node that uses an absolute path expression will start on the root node of the context node. Therefore, you will receive all three rows for every context node returned by nodes().

SELECT T.c.query('/Root/row') AS result
FROM   @x.nodes('/Root/row') T(c)
GO
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />

Note that the column returned by the nodes() method of the xml data type cannot be used directly. For example, the following query returns an error:

...
SELECT T.c
FROM   @x.nodes('/Root/row') T(c)

In the following query, the value() and query() methods of the xml data type are applied to the rowset returned by the nodes() method. The value() method returns the id attribute of the context item (<row>) and the query() method returns the <name> element subtree of the context item.

DECLARE @x xml 
SET @x='
<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>Joe</name></row>
    <row id="3" />
</Root>
'
SELECT T.c.value('@id','int') as id,
       T.c.query('name') as NAME
FROM   @x.nodes('/Root/row') T(c)
GO

This is the result:

 id  NAME
-----------------------
 1   <name>Larry</name>
 2   <name>Joe</name>
 3

Note that the result includes row ID 3 and that the <row> element does not have a <name> child. If you want to filter the result to return or not return the rows without the <name> child, you can filter it in one of the following ways:

Use a predicate in the nodes() path expression, such as /Root/row[name].
Use the exist() method on the rowset.
Use CROSS APPLY.
Use OUTER APPLY.

The following query specifies the exist() method against the rowset returned by nodes(). The exist() method returns True if the context node (<row>) has a <name> child.

DECLARE @x xml        
SET @x='<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id       
FROM @x.nodes('/Root/row') T1(rows)       
WHERE T1.rows.exist('name') = 1;       
GO

This returns two rows, row IDs 1 and 2.

The following query uses OUTER APPLY. OUTER APPLY applies nodes() to each row in T1(rows) and returns rows that produce the result set and also NULL. Therefore, the WHERE clause is used to filter rows and retrieve only rows where the T2.names column is not NULL.

DECLARE @x xml        
SET @x='       
<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id       
FROM @x.nodes('/Root/row') T1(rows)       
OUTER APPLY T1.rows.nodes('./name') as T2(names)       
WHERE T2.names IS NOT NULL        
GO

The following query uses CROSS APPLY. CROSS APPLY applies nodes() to each row in the outer table, T1(rows), and returns only the rows that produce a result set when nodes() is applied to T1.rows. In this case, you do not have to use the WHERE clause to test the IS NOT NULL.

DECLARE @x xml        
SET @x='<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id       
FROM @x.nodes('/Root/row') T1(rows)       
CROSS APPLY T1.rows.nodes('./name') as T2(names)       
GO

For information about CROSS APPLY and OUTER APPLY, see Using APPLY.

B. 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. For more information, see xml Data Type Representation in the AdventureWorks Database.

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. Therefore, the nodes() function gives a set of <Location> context nodes.

The query() method against this rowset requests self::node and, therefore, 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 the following:

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 the Instructions 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="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7

This is the partial result:

<MI:Location LocationID="10"  ...>
   <MI:step ... />
      ...
</MI:Location>
<MI:Location LocationID="20"  ... >
    <MI:step ... />
      ...
</MI:Location>
...

The following query is similar to the previous query, except it uses value() and query() to retrieve a set of values by using the context nodes in the rowset. For each location, the SELECT clause retrieves the Location ID and the tools used at that location.

SELECT C.value('@LocationID','int') as LId,
       C.query('declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                 MI:step/MI:tool') as result
FROM    Production.ProductModel
CROSS APPLY Instructions.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location') as T(C)
WHERE ProductModelID=7

The following is the result. For readability, namespaces are not shown.

 LId  result
 10  <MI:tool xmlns:MI="...">T-85A framing tool</MI:tool>
     <MI:tool xmlns:MI="...">Trim Jig TJ-26</MI:tool>
     <MI:tool xmlns:MI="...">router with a carbide tip 15</MI:tool>
      <MI:tool xmlns:MI="...">Forming Tool FT-15</MI:tool>
 20
 30  <MI:tool xmlns:MI="...">standard debur tool</MI:tool>
 45  <MI:tool xmlns:MI="...">paint harness</MI:tool>
 50
 60

C. 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 the Instructions column and returns the T1 (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 the T1 (Locations) rowset and returns the T2 (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="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";       
/MI:root/MI:Location') as T1(Locations)       
CROSS APPLY T1.Locations.nodes('       
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";       
./MI:step ') as T2(steps)       
WHERE ProductModelID=7       
GO

This 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 (
   'http://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

The following query is similar to the previous one, except that it applies the exist() method to the XML in the T2(steps) rowset to retrieve only the manufacturing steps that use at least one manufacturing tool. That is, the <step> element has at least one <tool> child.

WITH XMLNAMESPACES (
   'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS MI)

SELECT ProductModelID, 
       Locations.value('./@LocationID','int') as LocID,
       steps.query('.') as Steps
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
AND    steps.exist('./MI:tool') = 1
GO

A. Using nodes() method against a variable of xml type

B. Specifying the nodes() method against a column of xml type

C. Applying nodes() to the rowset returned by another nodes() method

Concepts

Other Resources

Help and Information