SQL Server - How to export the contents of a column to XML files

Views: 240
Reading Time: 4 minutes

Hey guys!!
On Thursday (09/04), I saw a very cool question in the Telegram group SQL Server - DBA, the largest group of DBA's and SQL Server Developers in the world, where the person had a table in the bank, where a column was of the XML type, and he would like to export to disk, each row of that table as a separate XML file and I found that would give a cool article to demonstrate that solution.

To export the table data to text files on the disk, I will use the solutions shared in the article SQL Server - How to export database data to text file (CLR, OLE, BCP).

If you are interested in learning how to better manipulate and treat XML files within SQL Server, I suggest reading the article SQL Server - How to read, import, and export data from XML files.

For this example, I will use the script below to create a demo table, simulating the original problem table:

Table example:

How to export the contents of a column to XML files

As part of the proposed solution for this scenario, I will use the Stored Procedure stpWrite_File_File, available in the article File Operations Using OLE Automation in SQL Server, to create text files with the contents of the columns:

Click to view the Stored Procedure code

After creating this Stored Procedure, create the destination directory of the XML files that will be generated and run the script below:

After running the script below, making the necessary adjustments for your scenario, the destination directory must have been populated with XML column content, with one file for each row in the table.

I hope you enjoyed this quick and very useful tip on a daily basis, especially for those just starting out.

A big hug and until next time!