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