使用xml方法在SQL Server中列出XML实体的方法？

SQL Server 2005/2008.

SQL Server 2005/2008。

I've searched and read several questions here and elsewhere and in BOL, but none I've found so far directly answer this question.

我在这里和其他地方以及BOL中搜索并阅读了几个问题，但到目前为止我找不到任何问题直接回答这个问题。

Given

特定

declare @xml xml = '<Root> <Ent foo="abc" bar="def" /> </Root>'

Is there a way to get a result set of something like

有没有办法得到类似的结果集

col1        col2
-----------------
foo         abc
bar         def

I know how to do this by using sp_xml_preparedocument and OPENXML, I'm just wondering if there's a way to do it directly using the xml methods. I haven't been able to find anything in BOL or Googling, but just wanted to make sure I didn't miss something.

我知道如何通过使用sp_xml_preparedocument和OPENXML来做到这一点，我只是想知道是否有办法直接使用xml方法。我无法在BOL或谷歌搜索中找到任何东西，但只是想确保我没有错过任何东西。

Thanks!

谢谢！

2 个解决方案

#1

In general, query() method works slower then value(). So i recommend use value() method:

通常，query（）方法比value（）工作得慢。所以我建议使用value（）方法：

 SELECT
    b.value('local-name(.)','nvarchar(MAX)') as col1,
    b.value('data(.)','nvarchar(MAX)') as col2
FROM @xml.nodes('//Root/Ent/@*') a(b)

Comparing with previous answer, it is about 3 times faster.

与之前的答案相比，它快了约3倍。

#2

This might give you a starting point to get you what you need.

这可能会为您提供一个起点，以满足您的需求。

DECLARE @xml xml = N'<Root> <Ent foo="abc" bar="def" /> </Root>';

WITH [Attributes]([xml])
AS
(
    SELECT
        @xml.query
        ('
            for $x in (/Root/Ent/@*)
            return <attribute name="{local-name($x)}" value="{data($x)}"/>
        ')
)
SELECT
    [Attribute].[data].value(N'@name', N'nvarchar(max)') AS [col1],
    [Attribute].[data].value(N'@value', N'nvarchar(max)') AS [col2]
FROM
    [Attributes]
CROSS APPLY
    [xml].nodes(N'attribute') AS [Attribute]([data]);

#1