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

Question

The database MS SQL Server 2008 R2does not have a column that is stored as a BLOB (it has a data type of "image"). I also know that BLOB contains XML.

Can reporting services be provided in any way to extract this information? How can I query or use data that is stored in XML BLOB?

I read something about casting an SQL IMAGE data type into an XML data type; then use @xml.queryto get element / attribute values, would that be a good idea? How can I apply a BLOB (image data type) to an XML data type? How to check if my XML has a schema or not (hence the typed or untyped XML)?

Or are there any simpler ways to retrieve data in BLOBs that contain XML in order to use it for generation SSRS web report?

Answer 1

Yes, you can drop Imagebefore Xmland then use functions such as XQuery to parse the XML document. However, you will, however, need to pass it through VARBINARY.

SELECT CAST(cast(SomeXmlStoredAsBlob AS VARBINARY(MAX)) AS XML) AS MyXml
FROM MyTable

I would recommend changing the type of the ASAP column - if all the data is Xml, and then converting it to Xml in accordance with the above, and if there are different formats, use VARBINARY(MAX)

SqlFiddle here

Answer 2

Yes, you can use SQL Server to retrieve data from your XML blog into a table.

blob, xml, - .

:

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

:

, ?

, .

, "" SQL Server.

XML-...