SQL Server 2008 R2：使用存储为BLOB的XML（图像数据类型）

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.

MS SQL Server 2008 R2上的数据库有一个存储为BLOB的列(具有“图像”数据类型)。我也知道BLOB包含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?

报告服务有没有办法提取这些信息?我如何查询或使用XML BLOB中存储的数据?

I have read something along the line of casting the SQL IMAGE data type to the XML data type; then use @xml.query 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)?

我已经阅读了将SQL IMAGE数据类型转换为XML数据类型的内容;然后使用@ xml.query获取元素/属性值,这是个好主意吗?如何将BLOB(图像数据类型)转换为XML数据类型?如何检查我的XML是否具有模式(因此键入或非类型化XML)?

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

或者是否有更简单的方法在包含XML的BLOB中检索数据以使用它来生成SSRS Web报告?

2 个解决方案

#1

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.

是的,您可以将Image转换为Xml,然后使用XQuery等功能来解析Xml文档。但是,您首先需要通过VARBINARY进行转换。

SELECT CAST(cast(SomeXmlStoredAsBlob AS VARBINARY(MAX)) AS XML) AS MyXml
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)

我建议尽快更改列类型 - 如果所有数据都是Xml,然后按照上面的说法将其转换为Xml,如果有不同的格式,则使用VARBINARY(MAX)

SqlFiddle here

#2

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

是的,您可以使用SQL Server将XML blob中的数据提取到表中。

You need to read in your blob value, then convert it into a variable of type xml, and from there, you can use some complicated syntax to query it.

您需要读取blob值,然后将其转换为xml类型的变量,并从那里,您可以使用一些复杂的语法来查询它。

Here's a little example:

这是一个小例子:

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)

This gives us these results:

这给了我们这些结果:

Cool, hey ?

很酷,嘿?

The downside is that this isn't particularly fast.

缺点是这不是特别快。

In my app, I use code like this, but write out the "table version" of this data into a regular SQL Server table.

在我的应用程序中,我使用这样的代码,但将此数据的“表格版本”写入常规SQL Server表。

You really don't want to be trying to do reporting based on directly loading and parsing XML blobs...

您真的不想尝试基于直接加载和解析XML blob进行报告...

#1