Export XML Columns to Individual Files

I was reading SqlServerCentral.com Articles Requested forum and saw this request.

Looking for an article that takes data from an XML column in a table and exports each field as a separate document.

While the request did indicate a PoSH or SSIS method, I wanted to give it a try using T-SQL. Lets look at how I managed it.

 

The Setup

To get started, I do some requisite cleanup, declare a few variables and create a simple table to hold our XML column that we will be exporting.

For data, lets query some of the system tables. In this case I’m going to create one XML document for Databases, Users, and Jobs.

 

And finally, we can use BCP to get our columns to disk:
 

Using this procedure we can pass in a document name, and the location we wish to save the XML file and get our results by calling the procedure like this:

Which results in three new files sitting in our C:\Temp directory:

Xml_output

The contents of those files are simply the XML we have stored in the XML typed document column.

XML_JobOutput

Things to Think About

This approach is demonstrating the basic mechanics of how it would work, but there are a few things to keep in mind. Many DBAs will restrict xp_cmdshell, as they rightly should, as it can be very dangerous in the wrong hands, but with proper precautions and monitoring, it is a valid tool in your tool belt. One final thing to think about is permissions. When xp_cmdshell is run, it runs as the SQL Server Service Account, unless configured more precisely with sp_xp_cmdshell_proxy for instance. Which ever account that is being used will need permissions to write to the @Location you provide.

Comments, Suggestions

This is my approach using T-SQL, I’d love to hear feedback on my approach or holes in where my approach might be wrong. Thanks for stopping by.

  • Pokehuinq

    Hi Jason. I do not see anything past the “And finally, we can use BCP to get our columns to disk:” part of the article. Thanks!

  • Looks like my WordPress was acting up on saving the code sample, quite odd. I think I’ve got it working, give it a review now.