Export and Import Xml using TSQL

For this example we’ll be using our old favourite the Microsoft sample Northwind database.

You can download the Northwind database installer here:

http://www.microsoft.com/downloads/en/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Exporting Xml using TSQL

We’ll be using xp_cmdshell and BCP to export the data so you’ll need to configure your Sql Server instance to allow you to run the command.  You can do that by running the following script:

To start with I’ll show the data we plan to export in a standard denormalised format from a Sql query:

Which returns us the data in the following format (for now I’ll only show the top of the results):

To return the same data in Xml that is nicely formatted and nested we can run the following TSQL statement:

Which gives us our Xml representation (again, I’ll only show the top of the results):

To export our data in Xml format we can run the following script:

Notice in the BCP statement we are using the –w parameter to indicate we want the output in Unicode.  In SQL 2008 R2 to boost performance we can change this to use the Native SQL format but more on that later.

Note: You’ll need to ensure you’re Sql Server instance is configured correctly with the appropriate permissions assigned set to run the xp_cmdshell and BCP statement, with access to the filesystem.

You can open our new C:\data.xml file in Notepad or IE and see we have nicely formatted xml file:

Importing Xml using TSQL

To import the data back into SQL you can use the following statement:

Which results in:

SQL 2008 R2 Native Format Support

For improved performance in SQL 2008 R2 you can alter the scripts above to utilise Native format support.

For export if you change the BCP statement in our export script above to use –N instead of –w:

You won’t be able to view the data in a text editor as it’s now in a SQL encoding but you’ll get much improved performance.

For import you can change the script to reference the data file type of ‘widenative’:

More information can be found about Using Native Format for Import and Export here:

http://msdn.microsoft.com/en-us/library/ms191232.aspx

I hope you found this blog post useful, happy coding!