Project relational data to XML column - is it possible?

Question

I have 2 tables of the following structure:

People

Id | LastName | FirstName | Other columns...

The second table has an XML column:

Id | MyXmlCol | Other columns...

MyXmlCol stores the following XML:

<myData>
  <block>
    <person id="1" />
    ...other nodes
  </block>
  ...other blocks
</myData>

The id attribute points actually to the Id column of the People table.

What I need, is to query MyXmlCol, so that it returns:

<myData>
  <block>
    <person id="1" LastName="Jones" FirstName="Bob" />
    ...other nodes
  </block>
  ...other blocks
</myData>

Is it possible to make such projection? I'm using Sql Server 2012.

Answer 1

If there can be only one element "person" in a single element "block", this should suit:

update T
set MyXmlCol.modify('
    insert (
        attribute LastName {sql:column("P.LastName")},
        attribute FirstName {sql:column("P.FirstName")}
    )
    into (/myData/block/person)[1]')
from @Table2 T
inner join @People P on T.MyXmlCol.value('(/myData/block/person/@id)[1]', 'int') = P.Id