Unfortunately, using tsql, it isn't possible (easily that is) to add an attribute into every child node as the target of an insert operation needs to be a single node.

Personally, if I had a similar requirement to add an attribute into all child nodes of any given/unknown xml structure, then I would probably look at using an xslt to do a transform on the xml rather than tsql. Otherwise to try and achieve this in tsql, you are going to have to come up with a pretty complex dynamic based SQL script that reads the structure of the xml, shred it in some part and then perform lots of dynamic xml modify operations against the xml structure to add the attributes to the elements.

I did blog about xml dml basics http://www.olcot.co.uk/sql-blogs/xml-dml---using-the-modify-method-part-1-delete and there is a part on adding attributes to elements, but i'm guessing this isn't going to help you here.

If you know what the xml structure is before hand then it may not be so bad to shred it to a flat table and then regenerate it with the attributes, but without knowing how big/complex your xml structures are, i can't really advise on that as a potential way forward. Also, if you don't know what the xml structures are going to be at runtime, then this isn't going to be an option.