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’
                <node attribute1=”value”/>
                <node attribute1=”othervalue”/>
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.

Tags: , ,

One response to “Transform XML data into relational data”

  1. Candy Paint says :

    -~- I am really thankful to this topic because it really gives great information -;,

Leave a Reply to Candy Paint Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: