SQL - Fetch XML with an INNER JOIN, modifying the XML

Question

I have a table in SQL Server 2008 that stores XML data in an XML column. A typical XML fragment is:

<validation>
    <Field1 author="56234" date="20120101" />
    <Field2 author="23232" date="20120101" />
        [...etc...]
</validation>

What I'm trying to work out - and I can't see how to do it - is to select this data with an INNER JOIN to another table and modify the XML data in the result set, i.e., so I get this:

<validation>
    <Field1 author="56234" date="20120101" authorFullName="Bob Smith" />
    <Field2 author="23232" date="20120101" authorFullName="Jane Hill" />
        [...etc...]
</validation>

Now I know I can do a CROSS APPLY to pull the XML data into a recordset directly and inner join - for example with:

select xmldata.a, people.personname
    from xmldata
        cross apply xmldata.x.nodes('/validation/node()') vdata(fielddata)
        inner join people 
            on people.personid = vdata.fielddata.value('@author','NVARCHAR(20)')

But what I actually want to do is return the original XML but with a new attribute added, mapping people.PersonName into the new @authorFullName attribute.

I can't quite work out the syntax (or even if it's indeed possible). I'm assuming I'd cross apply to do a modify with insert attribute - something based on

select xmldata.a, xmldata.x
    from xmldata
        cross apply xmldata.x.modify('insert attribute authorFullName {sql:column("people.personfullname")} into /validation/node()')
        inner join people 
            on people.personid = [...what goes here?...]

but getting the syntax right is escaping me. I'm increasingly thinking it's not possible, and I'm better off doing this in two queries and merging the results in non-SQL business logic.

Answer 1

You can not use modify in a select statement.

From modify() Method (xml Data Type)

The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

I think you have two options.

1. Shred the XML and use for xml path to rebuild the XML document the way you want and insert person name in the appropriate place.
2. Extract the XML to a variable and use set @XML.modify(insert... to insert person name.

If you go for the second option you have to use a while loop because expression2 in insert (XML DML) has to be a single node.

The code for option 2 could look something like this.

declare @XML xml
declare @C int
declare @PersonName varchar(50)
declare @PersonID int

-- Store your XML in a XML variable
set @XML = 
'<validation>
    <Field1 author="56234" date="20120101" />
    <Field2 author="23232" date="20120101" />
</validation>'

-- Get number of nodes to be modified
set @C = @XML.value('count(/validation/*)', 'int')

-- For each node
while @C > 0
begin
  -- Get person id from XML
  set @PersonID = @XML.value('(/validation/*[sql:variable("@C")]/@author)[1]', 'int')

  -- Get person name
  select @PersonName = personname
  from people
  where personid = @PersonID 

  if @@rowcount = 1
  begin
    -- add person name to XML
    set @XML.modify('insert attribute authorFullName {sql:variable("@PersonName")} 
                        into (/validation/*[sql:variable("@C")])[1]')
  end

  -- next node
  set @C = @C - 1
end