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;
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'
where JobCandidateID = 1;
insert dbo.Documents(Name, file_stream) values(@name, @resume);
Now XML files can get out!