Join on data from XML in T-SQL
22,840
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')
Comments
-
Marian Zagoruiko about 4 years
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?