Maytas Monsereenusorn Maytas Monsereenusorn - 11 months ago 65
SQL Question

SQL Server 2008 R2: Working with XML stored as BLOB (image data type)

A database on the

MS SQL Server 2008 R2
has a column which is stored as a BLOB (has a "image" data type) . I also know that the BLOB contains XML.

Is there any way for reporting services to extract this information?
How would I query or use the data that is stored within the XML BLOB?

I have read something along the line of casting the SQL IMAGE data type to the XML data type; then use
to get element/attribute values, would this be a good idea? How would I cast the BLOB (image data type) into XML data type?
How do I check if my XML have a schema or not (hence typed or untyped XML)?

Or are there easier ways of retrieving data within the BLOB that contains XML to use it to generate a
SSRS web report

Answer Source

Yes, you can cast the Image to Xml and then use features such as XQuery to parse the Xml document. You will however first need to cast it through VARBINARY as well.

FROM MyTable

I would recommend changing the column type ASAP - if all data is Xml, then convert it to Xml as per above, and if there are different formats, then use VARBINARY(MAX)

SqlFiddle here