如何将存储在 NVARCHAR 类型的列中的 XML 元素提取到单独的列中

Question

>场景：在SQL Server中，我有一个表[users]，其中包含列[id] int和userdetails nvarchar(max)，其中包含以下数据：

<Attributes>
<Map>
<entry key="displayName" value="Administrator"/>
<entry key="email" value="[e-mail]"/>
<entry key="firstname" value="Joe"/>
<entry key="lastname" value="Blow"/>
</Map>
</Attributes>

在我想提取为单个列的查询中，如下所示：

displayName | email | firstname | lastname

我可以使用什么语法来完成此操作？(最好以高性能的方式，因为我有很多查询要对潜在的大表执行......但即使让它工作也会很可爱(

到目前为止，我尝试过：2 +小时的谷歌搜索并尝试各种不同的语法都没有成功(由于各种错误消息而失败，每个错误消息的解决方法也不成功(。

次要问题可能是是否有办法容纳某些行中可能缺少的键值(即：可能根本没有指定电子邮件(

Answer 1

您可以使用 SQL XML 函数和 XPath 查询。这是一个例子。

declare @tbl table(id int, userdetails nvarchar(max))
insert @tbl(id,userdetails)
values(1,'<Attributes>
<Map>
<entry key="displayName" value="Administrator"/>
<entry key="email" value="[e-mail]"/>
<entry key="firstname" value="Joe"/>
<entry key="lastname" value="Blow"/>
</Map>
</Attributes>')
;with tbl as (
select id,cast(userdetails as xml) ud
from @tbl)
select id,
t.v.value('entry[@key="displayName"][1]/@value','nvarchar(100)') displayName,
t.v.value('entry[@key="email"][1]/@value','nvarchar(100)') email,
t.v.value('entry[@key="firstname"][1]/@value','nvarchar(100)') firstname,
t.v.value('entry[@key="lastname"][1]/@value','nvarchar(100)') lastname
from tbl 
cross apply ud.nodes('Attributes/Map') t(v)