在T-SQL中联接来自XML的数据 [英] Join on data from XML in T-SQL
本文介绍了在T-SQL中联接来自XML的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下XML消息:
DECLARE @XML AS XML
SET @XML =
'<Message>
<Changes>
<Deleted>
<ROW id="1" name="Nicole" surname="Bartlett" city="denver" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" />
<ROW id="1" name="Nicole" surname="Bartlett" city="boston" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" />
</Deleted>
<Inserted>
<ROW id="1" name="Nicole" surname="Bartlett" city="denver" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" />
<ROW id="1" name="Nicole" surname="Bartlett" city="boston" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" />
</Inserted>
</Changes>
</Message>'
我需要从此消息中选择数据并在id字段上加入另一个表.以下代码不起作用:
And I need to select data from this message and join another table on id field. The following code doesn't work:
SELECT T.c.value('./@id', 'int') as id, t.c.value('./@name', 'varchar(max)') as name
FROM @XML.nodes('/Message/Changes/Deleted/ROW') T(c)
inner join other_table tbl
on tbl.id = id
我该怎么做?
推荐答案
SELECT T.c.value('./@id', 'int') as id, t.c.value('./@name', 'varchar(max)') as name
FROM @XML.nodes('/Message/Changes/Deleted/ROW') T(c)
inner join other_table tbl
on tbl.id = T.c.value('./@id', 'int')
这篇关于在T-SQL中联接来自XML的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文