Store XML Document as Binary, Read as XML
I had an interesting question the other day, so I thought I would share both the question and the answer.
We had an older application that had stored an XML document in a table as a binary data type column. I know, I know – we’ve had an XML data type for some time, but not when this app was written. So of course the tables just moved along with the app, even though there was a better way to do it. The question I got was, can I read the binary field as XML, even though it isn’t?
The answer is: “It Depends”. No, that’s just the answer I always give. In this case, I was able to do it easily. If you want to try a test yourself, just create an XML document in a C:\temp directory (on a test system, your mileage may vary, don’t run with scissors) and then run this code:
/* Need a test table to work with */
CREATE TABLE TestTable(DocumentName varbinary(max)) ;
GO
/* I'll load it up with an XML document, but store that as binary */
INSERT INTO TestTable(DocumentName)
SELECT *
FROM OPENROWSET(BULK N'C:\temp\Test.xml', SINGLE_BLOB) AS Image;
GO
/* Let's see it in text */
SELECT CAST(TestTable.DocumentName AS varchar(max))
FROM TestTable;
GO
/* And in XML*/
SELECT CAST(TestTable.DocumentName AS XML)
FROM TestTable;
GO
There are other possible solutions as well. This worked for my situation.
