在SQL Server 2008中将XML变量转换为结果集

Question

想象一下，我有一些XML，看起来像这样：

DECLARE @xml XML
SET @xml = CAST(N'<row><id>1</id><a>b</a></row><row><id>2</id><a>x</a></row>' AS XML)

即：

<row>
    <id>1</id>
    <a>b</a>
</row>
<row>
    <id>2</id>
    <a>x</a>
</row>

id元素是每一行的键，它可能是动态的，所以我把它存储在一个变量中：

DECLARE @idname NVARCHAR(MAX)
SET @idname = N'id'

我想要一个包含两列的结果集，即id值和行的内容。我试过这个：

SELECT node.value(N'xxx', N'NVARCHAR(MAX)') AS key, CAST(node.query(N'.') AS NVARCHAR(MAX)) AS value
FROM @xml.nodes(N'/row') AS roots(node)

问题是，我不知道该把什么放在值的内部，而不是xxx。我希望能够做一些类似的事情：

node.value(@idname, N'VARCHAR(MAX))

因此，我的变量将被计算为XPath表达式，但我知道这是不可能的。如果我改为使用：

node.value(N'sql:variable("@idname")', N'NVARCHAR(MAX)')

它将返回@idname变量中的值，而不是对其求值。

我想要的是：

--------------------------------------
key           |value
--------------------------------------
1             |<id>1</id><a>b</a>
2             |<id>2</id><a>x</a>

那么，这有可能吗？

谢谢！

Answer 1

select T.N.value('(*[local-name(.)=sql:variable("@idname")])[1]', 'int') as [key],
       T.N.query('*') as value
from @xml.nodes('/row') as T(N)