Using a SQL Server FileTable to get XML, Text, Binary, etc out of SQL Server (think &quot;Kind of a reverse of OPENROWSET&quot;)

Friday, July 13, 2012

Using a SQL Server FileTable to get XML, Text, Binary, etc out of SQL Server (think "Kind of a reverse of OPENROWSET")

Bob Beauchemin's Blog - 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. ...

Awesome. And what's even more awesome is that you can use this, with some tweaking, to get even the old, depreciated, data types, like TEXT and nTEXT (and probably IMAGE), out too.

Here's a snap of my experimenting with this;

And here's my using a TEXT data type (with a lame tweak in the middle to "convert the TEXT to a VARCHAR. I'm sure there's a better way, but I only had a few seconds to play with this...);

The important thing is that this just worked and is a pretty darn easy way to get data out of SQL Server and only the file system without writing a line of code (beyond TSQL). I never would have thought of using a FileTable like this without Bob's post. MAN, I love the Internet! :)