在T-SQL中加入XML中的数据

I have the following XML message:

我有以下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>'

And I need to select data from this message and join another table on id field. The following code doesn't work:

我需要从此消息中选择数据并在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 = id

How can I do this?

我怎样才能做到这一点？

1 个解决方案

#1

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')