将SQL 2005中的XML数据类型转换为关系结果集

I have a table in a SQL 2005 database that contains a column defined as a xml datatype. I'm trying to write stored proc that queries the xml and returns a resultset from a select statement. I've seen examples of returning scalar values or xml but not how to return a resultset.

我在SQL 2005数据库中有一个表,其中包含一个定义为xml数据类型的列。我正在尝试编写查询xml的存储过程并从select语句返回结果集。我已经看到了返回标量值或xml但不返回结果集的示例。

Am I going to have to use openxml or is there another solution?

我将不得不使用openxml还是有其他解决方案?

2 个解决方案

#1

The xml data type has its own set of methods that you can use to deal with the data in the column. In this case you'd probably use something like this:

xml数据类型有自己的一组方法,可用于处理列中的数据。在这种情况下,你可能会使用这样的东西:

select xmlColumnName.value('XpathExpression', 'outputSqlType') from dataTable

For example,

select xmlColumnName.value('/root/node[@name="myname"]', 'varchar(60)')

The methods are query(), value(), exist(), modify() and nodes(), and you can read more about them in Books Online.

方法是query(),value(),exist(),modify()和nodes(),您可以在联机丛书中阅读更多相关信息。

#2

You can use the xml data type or OPENXML. Another poster gave an xml data type example, so here is a OPENXML example.

您可以使用xml数据类型或OPENXML。另一张海报给出了一个xml数据类型示例,所以这里是一个OPENXML示例。

It really depends on the XML schema though. SQL Server may not like your schema very well. If you have any control over the schema then it helps. You may end up having to use a table variable and build the data with multiple calls to OPENXML to query everything.

这实际上取决于XML架构。 SQL Server可能不太喜欢您的架构。如果您对架构有任何控制权,那么它会有所帮助。您可能最终必须使用表变量并通过多次调用OPENXML来构建数据以查询所有内容。

For example, the XML input could be processed as follows:

例如,XML输入可以按如下方式处理:

DECLARE @idoc int
DECLARE @doc nvarchar(max)

SET @doc = '
<xml>
    <Entry Type="Error" Start="2008-11-19 02:16:00" End="2008-11-20 04:55:00" />
    <Entry Type="Success" Start="2008-11-25 12:45:00" End="2008-11-25 13:01:00" />
</xml>'


EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT   [EventType]
        ,[EventStart]
        ,[EventEnd]
FROM    OPENXML (@idoc, '//Event',1)
        WITH    ([Type]         varchar(30)
                ,[Start]        varchar(30)
                ,[End]          varchar(30)
)

One of the challenges I faced was parsing XML dates (ISO8601 with the 'T' and possible time zone). I ended up creating a .NET assembly with a single line of code to convert it a datetime that SQL liked.

我面临的挑战之一是解析XML日期(ISO8601带有'T'和可能的时区)。我最终用一行代码创建了一个.NET程序集,将它转换为SQL喜欢的日期时间。

#1