Store XML Document as Binary, Read as XML

  • Comments 2

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.

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • hey Bucky..

    I would love to see the performance implications of casting this data from binary on every query..

    or even better.. casting the data from binary and right after that doing some XML operations using value() or query()..

  • It's not too bad, but it's a one-time thing so we don't care in this case. Obviously the best answer (as I mentioned) is to use the proper data type, but in this case it was already in the "bad" thing and not easily changed. Once we do this workaround, we'll store it properly.

    Thanks for reading!

Page 1 of 1 (2 items)