Saving the contents of a SQL Server XML column to a file

While preparing for my TechEd talk on filetables and full-text search, I came across this tidbit that I thought was interesting.

With the advent of XML support in databases, specifically SQL Server's XML data type in SQL Server 2005, there has been two main ways to store XML; in files on the file system, and in a database. SQL Server has a built-in way to move XML data from files to the database, namely the OLE DB BULK provider and OPENROWSET SINGLE BLOB/CLOB/NCLOB. It looks like this

— Load XML from a file
DECLARE @x XML;
SET @x = ( SELECT * FROM OPENROWSET( BULK 'C:\invoice1.xml', SINGLE_BLOB) AS x);

— Or direct to table
INSERT INTO dbo.invoice_docs(invoice)
SELECT * FROM OPENROWSET(BULK 'C:\invoice1.xml', SINGLE_BLOB) AS x;
go

Because there is no corresponding built-in way to move XML from the database to files, I'd always referred to this asymmetry as "all your XML belong to us". Or, a bit more rudely, as "SQL Server is the roach motel of XML files, they get in, but they can't get out".

All joking aside, in SQL Server 2012, filetables provide a straightforward way to export XML column data (or any data for that matter) as files using only T-SQL. Suppose I have an have a filetable named Documents. This code stores the XML resume from the row with JobCandidateID of 1 to a file in the root directory of the filetable named "JobCandidate1.xml". To store this into a subdirectory using T-SQL, see the code in this blog entry. You can then copy the file to the file system location of your choice.

— get existing database blob into files
declare @resume varbinary(max);
declare @name varchar(40);

select @resume = cast(Resume as varbinary(max))
        ,@name = 'JobCandidate' + cast(JobCandidateID as varchar(1)) + '.xml'
from AdventureWorks.HumanResources.JobCandidate
where JobCandidateID = 1;

insert dbo.Documents(Name, file_stream) values(@name, @resume);

Now XML files can get out!

@bobbeauch

2 thoughts on “Saving the contents of a SQL Server XML column to a file

  1. Bob,

    That’s a pretty cool use of filetables. What would be more useful is if they added a CLR XmlDocument and XmlNode column types.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.