Copy attributes from source "table"

Question

I am trying to modify a chunk of xml and insert extra information without altering the original info.

Given an xml document such as the following:

<xml>
  <node att1="a" att2="b"/>
  <node att1="c" att99="d"/>
</xml>

I want to achieve the following output:

<xml>
  <node extra="hello" att1="a" att2="b"><More/></node>
  <node extra="hello" att1="c" att99="d"><More/></node>
</xml>

The part I'm stuck on is copying the existing attributes to the new xml tree.

SELECT  'hello' AS [@extra],
    T.C.query('@*') AS [*] --this line doesn't work
FROM    @xml.nodes('/xml/node') T(C)
FOR XML PATH ('xml')

I kind of hoped that the T.C.query('@*') AS [*] part would just copy the attributes but no joy.

How can I copy all the attributes without knowing their names?

I have solved my specific issue another way now (using string replace). It's a massive cludge but it does what I need (for now) — DJL, Jan 07 '14 at 12:40
You might want to post a separate question asking how to do this operation without a cludge — James A Mohler, Feb 23 '14 at 16:28

Mikael Eriksson · Answer 1 · 2014-06-19T11:48:09.127

You can rebuild the XML and insert the attribute using the query() function.

declare @XML xml = '
<xml>
  <node att1="a" att2="b"/>
  <node att1="c" att99="d"/>
</xml>'

select @XML.query('element xml {
                               for $n in xml/node 
                               return element node {
                                                   attribute extra{"hello"},  $n/@*
                                                   }
                               }')

Copy attributes from source "table"

1 Answers1