Transform XML data into relational data
May include but is not limited to: OPENXML, sp_xml_preparedocument, sp_xml_removedocument
SQL Server allows you to retrieve XML data as relational data (a process known as shredding) . To implement this behavior, you’d need three methods, mentioned in the “May include but is not limited to” section, but for the sake of Google and SEO: OPENXML for the SELECT clause, the sp_xml_preparedocument system stored procedure to prepare your documents, and the sp_xml_removedocument system stored procedure to remove the XML document from the server memory. This is essential, because your loaded XML documents will use server resources as long as the server won’t restarts, and you can easily run out of memory.
The syntax of sp_xml_preparedocument is somewhat easy. It accepts three values, and integer handle, which is an OUTPUT value, an nvarchar(max) which is the string representation of your XML document, and an optional flags parameter, which is very good, and certainly covered in SQL Server Books Online in great detail.
A simple example of using sp_xml_preparedocument:
DECLARE @docHandle int;
DECLARE @xmlData nvarchar(max) = N’
sp_xml_preparedocument @docHandle OUTPUT, @xmlData;
Now you can query the transformed data with the OPENXML clause. You need to pass the handle integer and a string literal into it, like the following:
SELECT * FROM OPENXML(@docHandle, ‘root/node’)
Which selects the preloaded document as any other SELECT statement. After you’ve done, call the sp_xml_removedocument stored procedure, which accepts our integer handle, to remove the cached data from the memory.