T-SQL Syntax for Node.XML

This section describes the T-SQL syntax for querying and manipulating XML data using the Node.XML methods and related constructs.

Overview of XML Support in T-SQL

SQL Server provides robust support for working with XML data directly within your database. You can store XML data in dedicated xml data type columns or in text-based columns. T-SQL offers specific functions and syntax extensions to query and transform XML data.

The Node.XML Method

The Node.XML method is a powerful tool for retrieving the XML value of an XML instance. It's often used in conjunction with XML variables or XML columns.

Syntax

                
                    DECLARE @MyXml XML;
                    SET @MyXml = '<root><element attribute="value">Content</element></root>';

                    SELECT @MyXml.value('.', 'NVARCHAR(MAX)');
                    -- Or for a specific node:
                    SELECT @MyXml.query('/root/element');
                
            

Parameters

• The period (.) signifies the context item, which typically represents the entire XML node or document itself when used without a path.
• The second parameter (e.g., 'NVARCHAR(MAX)') specifies the target data type to which the XML content should be cast.

Commonly Used XML Querying Methods

.value() Method

The .value() method is used to extract a scalar value from an XML instance. It requires an XQuery path expression and a target SQL data type.

                
                    SELECT
                        XmlColumn.value('(/Product/Name)[1]', 'NVARCHAR(100)') AS ProductName,
                        XmlColumn.value('(/Product/Price)[1]', 'DECIMAL(10, 2)') AS ProductPrice
                    FROM YourXmlTable;
                
            

Note: The [1] predicate is often used to ensure that only the first matching element is returned, as .value() expects a single scalar value.

.query() Method

The .query() method is used to extract an XML fragment from an XML instance. It takes an XQuery path expression and returns an xml data type.

                
                    SELECT
                        XmlColumn.query('/Product/Features') AS ProductFeatures
                    FROM YourXmlTable
                    WHERE XmlColumn.exist('/Product/Category[. = "Electronics"]') = 1;
                
            

.exist() Method

The .exist() method checks for the existence of nodes that match an XQuery path expression. It returns 1 if nodes exist and 0 otherwise.

                
                    SELECT *
                    FROM YourXmlTable
                    WHERE XmlColumn.exist('/Product[@id]') = 1; -- Checks if a product element has an 'id' attribute
                
            

.nodes() Method

The .nodes() method shreds an XML instance into a set of rows. Each row contains an XML fragment representing a node. This is often used with the CROSS APPLY operator.

                
                    SELECT
                        T.c.value('@sku', 'VARCHAR(50)') AS SKU,
                        T.c.value('(./Name)[1]', 'VARCHAR(100)') AS ItemName
                    FROM YourXmlTable
                    CROSS APPLY XmlColumn.nodes('/Products/Product') AS T(c);
                
            

Common XQuery Paths

• /root: Selects the root element.
• /root/element: Selects all element children of the root element.
• /root/element[@attribute='value']: Selects element children that have an attribute named attribute with the value 'value'.
• /root/element[1]: Selects the first element child of the root element.
• //element: Selects all element elements anywhere in the document.

Performance Considerations

When querying large XML datasets, consider indexing your XML columns using XML indexes to improve query performance. Use appropriate XQuery paths and methods to retrieve only the data you need.