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
- 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())', '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,
- 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
Price of each product. Here’s how:
SELECT T.C.value('(Name/text())', 'VARCHAR(50)') AS ProductName, T.C.value('(Price/text())', '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())', 'FLOAT') AS SalesPrice FROM Sales INNER JOIN Products P ON P.ProductName = T.C.value('(Name/text())', '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.