SQL Server XML nodes method

Practical queries and examples on how to use the SQL Server XML nodes() method.

The SQL Server landscape has steadily evolved to accommodate various data types, like the XML data type, including the nodes method. While the value() method has been a go-to for many when dealing with XML, it’s the nodes() method that allows for efficient row-wise decomposition of XML data into relational format. This guide aims to provide a detailed, technical walk-through of the XML nodes() method, explaining its capabilities, syntax, and practical applications in T-SQL.

Table of Contents

SQL Server XML nodes() method main features

Unlike the XML query() and the XML value() methods, that we covered in previous tutorials, the nodes() method serves multiple purposes, primarily, it can:

Transform XML data into a rowset format, which can be easily integrated into relational tables.
Navigate through repetitive XML elements to extract values into multiple rows.
Facilite complex joins between XML data and existing relational tables.

Create a sales sable with an XML column

Let’s work with a Sales table similar to our earlier example. This table has an XML column named SalesInfo. Open your SQL Server Management Studio (SSMS) and execute the following code:

CREATE TABLE Sales (
  SalesID INT PRIMARY KEY,
  SalesInfo XML
);

Populate the XML Sales table

Before diving into the technical aspects of the nodes() method, we need some sample data. We’ll insert multiple product details into the SalesInfo XML column.

INSERT INTO Sales (SalesID, SalesInfo)
VALUES (1, '<Products><Product><Name>Laptop</Name><Price>800</Price></Product><Product><Name>Mouse</Name><Price>20</Price></Product></Products>');

INSERT INTO Sales (SalesID, SalesInfo)
VALUES (2, '<Products><Product><Name>Smartphone</Name><Price>500</Price></Product><Product><Name>Charger</Name><Price>15</Price></Product></Products>');

Generic syntax of XML nodes() in T-SQL

The nodes() method has a general syntax pattern like the one below:

SELECT T.C.value('(NodeName/text())[1]', 'SQLDataType') 
FROM TableName.Column.nodes('XQueryExpression') AS T(C)

Let’s breakdown the different arguments of the previous piece of code:

T.C.value: This is the column alias, T, and the XML column alias, C.
XQueryExpression: The XPath expression that specifies the nodes to be targeted.

Decode multiple products with the nodes method

Let’s say you want to unpack the Products XML into individual rows, extracting the Name and Price of each product. Here’s how:

SELECT T.C.value('(Name/text())[1]', 'VARCHAR(50)') AS ProductName,
       T.C.value('(Price/text())[1]', 'FLOAT') AS ProductPrice
FROM   Sales
CROSS APPLY SalesInfo.nodes('/Products/Product') AS T(C)
WHERE  SalesID = 1;

Join XML data with relational data in T-SQL

The nodes() method is practical when you need to perform more complex tasks, like joining XML data with existing relational tables.

-- Assuming a Products table exists
SELECT P.ProductName, T.C.value('(Price/text())[1]', 'FLOAT') AS SalesPrice
FROM   Sales
INNER JOIN  Products P ON P.ProductName = T.C.value('(Name/text())[1]', 'VARCHAR(50)')
CROSS APPLY SalesInfo.nodes('/Products/Product') AS T(C)
WHERE SalesID = 1;

Conclusion about the T-SQL nodes method

The SQL Server XML nodes method is a powerful tool to transform XML data into a relational format, enabling intricate queries and joins with existing tables. As databases continue to evolve, incorporating both structured and semi-structured data, mastering XML methods like nodes() becomes increasingly important for modern database professionals.

To go further, check out this overview of the XML data types and queries using T-SQL.