title: Join on data from XML in T-SQL
body: <p>I have the following XML message:</p>
  
  <pre><code>DECLARE @XML AS XML
  SET @XML = 
  '&lt;Message&gt;
  &lt;Changes&gt;
      &lt;Deleted&gt;
          &lt;ROW id="1" name="Nicole" surname="Bartlett" city="denver" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" /&gt;
          &lt;ROW id="1" name="Nicole" surname="Bartlett" city="boston" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" /&gt;
      &lt;/Deleted&gt;
      &lt;Inserted&gt;
          &lt;ROW id="1" name="Nicole" surname="Bartlett" city="denver" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" /&gt;
          &lt;ROW id="1" name="Nicole" surname="Bartlett" city="boston" balance="779.4663" dateOfBirth="1991-12-11T14:05:42.830" maritalStatus="S" /&gt;
      &lt;/Inserted&gt;
  &lt;/Changes&gt;
  &lt;/Message&gt;'
  </code></pre>
  
  <p>And I need to select data from this message and join another table on id field. The following code doesn't work:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How can I do this?</p>