T-SQL provides different ways to work, not just with tables, but also with other data containers, such as XML, JSON, etc. In this article, we will talk about two different ways of XML shredding in T-SQL.

What is “shredding XML”?
XML shredding is a specific term for "transforming” XML content into table representation.

Preparation
First of all, we need some XML data. This can be any data (loaded from the internet, custom defined, etc.).

Also, you can store the XML in your drive, directly insert it into your table, or at least define an XML variable and store it inside.

Loading XML
I stored my XML in drive C:/ and using OPENROWSET, and stored it in the @xmlContent variable.

Shredding using OPENXML
The first way of shredding XML is using the OPENXML command. It needs some additional preparation, but we can load big XML content at one time and do any number of manipulations between the scope. Before “parsing” XML to a table, we must prepare XML for parsing. sp_xml_preparedocument is a stored procedure that helps to load XML content into SQL SERVER’s memory (cache). This procedure also provides a way to access already loaded xml using a user-defined handler. This handler is a “pointer” to the memory where we loaded our XML file. This means that there is no need to use the XML variable after retrieving the handler.

After finishing our operations, we need to clear the cache. For that, we have the sp_xml_removeddocument stored procedure.

Shredding using XQuery
XQuery is also a powerful tool when it comes to shredding XML. It is more flexible and relevant for parsing small XML content. As opposed to OpenXml, XQuery doesn’t require any preparation. So, there is no need to build in any procedures to shred the content. XQuery provides special methods to work with XML . In most case we use nodes() and value() to read data.

Using XQuery and OpenXML, we can “convert” XML to table. In technical literature, we call it “shredding”. OpenXML allows you to work with big XML content, but it requires XML to be loaded into memory entirely. On the other hand, XQuery doesn’t load anything into memory and is faster with working small XML data.

HostForLIFEASP.NET SQL Server 2021 Hosting