Join on data from XML in T-SQL

join, sql-server, t-sql, xml

Solution

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

Problem

I have the following XML message: ``` 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: ``` 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?

Original source