Shredding XML in SQL Server 2017

=====================================================

XML data types and manipulation capabilities have been a part of the Microsoft SQL Server series for several years. The introduction of XML data type in SQL Server 2005 brought significant changes to how developers interact with XML documents within their applications. This article delves into shredding XML in SQL Server 2017, focusing on its syntax and implementation details.

Understanding InputXML

InputXML is a table-valued XML data type that represents an external source of XML data, which can be used in various queries such as insert statements. It allows developers to easily integrate external XML data with their T-SQL code without the need for string manipulation or XSLT transformations.

The key characteristics of InputXML include:

• It supports both relational and hierarchical query structures.
• The nodes of an InputXML table can be accessed using the nodes() method, similar to other XML data types in SQL Server.
• The inputxml property is used to access the external source of XML data.

Shredding XML

Shredding XML refers to the process of breaking down a complex XML document into smaller, more manageable pieces. This allows developers to work with individual elements or attributes as needed, rather than dealing with a single large XML document.

In SQL Server 2017, shredding XML can be achieved using the cross apply operator in conjunction with the nodes() method of InputXML.

The basic syntax for shredding XML is as follows:

SELECT column_name
FROM table
CROSS APPLY table_inputxml.nodes('/path_to_xml_element') AS xml_table(xml_column)

SQL Query

Given an example XML document, we want to extract the value of the ID, StartDate, and EndDate columns. The first query provided by the OP attempts to accomplish this using cross apply with inputxml.nodes():

-- DDL and sample data population, start
DECLARE @tbl TABLE (InputXML xml)

INSERT INTO @tbl (InputXML)
VALUES ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <document>
        <table name="tableName1">
            <column name="ID">000010313500011171011710001</column>
            <column name="StartDate">10/27/2019</column>
            <column name="EndDate">11/02/2019</column>
        </table>
    </document>
');

-- DDL and sample data population, end

SELECT px1.tbl.value('@name','nvarchar(50)') as TableName
     ,px2.col.value('@name','nvarchar(50)') as ColName
FROM #testXML px
CROSS APPLY   inputxml.nodes ('/document/table') as px1(tbl)
CROSS APPLY   inputxml.nodes ('/document/table/column') as px2(col);

However, the provided query has several issues:

• It doesn’t correctly specify the path for nodes(), leading to potential errors.
• The column names (px1.tbl.value('@name','nvarchar(50)') and px2.col.value('@name','nvarchar(50)')) do not accurately represent the desired output.

The corrected query leverages the correct syntax and provides the expected output:

-- DDL and sample data population, start
DECLARE @tbl TABLE (InputXML xml)

INSERT INTO @tbl (InputXML)
VALUES ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <document>
        <table name="tableName1">
            <column name="ID">000010313500011171011710001</column>
            <column name="StartDate">10/27/2019</column>
            <column name="EndDate">11/02/2019</column>
        </table>
    </document>
');

-- DDL and sample data population, end

SELECT col.value('(column[@name="ID"]/text())[1]','nvarchar(50)') as ID
     , col.value('(column[@name="StartDate"]/text())[1]','DATE') as StartDate
     , col.value('(column[@name="EndDate"]/text())[1]','DATE') as EndDate
FROM @tbl tbl
    CROSS APPLY tbl.InputXML.nodes('/document/table') AS tab(col);

Output

The corrected query produces the expected output:

+-----------------------------+------------+------------+
|             ID              | StartDate  |  EndDate   |
+-----------------------------+------------+------------+
| 000010313500011171011710001 | 2019-10-27 | 2019-11-02 |
+-----------------------------+------------+------------+

Conclusion

In this article, we explored shredding XML in SQL Server 2017, a crucial skill for developers working with complex data structures. By leveraging the cross apply operator and understanding how InputXML nodes can be accessed, developers can effectively break down large XML documents into smaller, more manageable pieces.

The corrected query demonstrates a key principle of using InputXML: correctly specifying the path for nodes() to avoid potential errors. This understanding will allow developers to work efficiently with XML data in SQL Server 2017 and beyond.

Last modified on 2023-07-24