How do I append / merge additional XML into an existing XML field in SQL Server 2005

Question

I have a column in SQL Server 2005 that stores a simple chunk of XML. At a later point processing is performed and I need to merge some processing info into the XML.

While I can do this at an intermediate point I would much prefer to keep this method centraliazed within the stored procedure that is responsible for updating other fields post-processing.

Here's an example of the XML I'm starting with and the type of outcome I'd like to achieve. Can anyone provide me some rough SQL to achieve it?

Update: Finally got it! I'll post the full solution when I get the chance, it was enough of a hack that someone else will hopefully find it useful

All finished! In the end I had a couple of additional requirements that required me to rework Marc's suggested solution and ditch the .modify() function entirely; however his answer let me get past my initial hurdles and got me to where I could step back and spot the easier approach. Here's my final solution!

Answer 1

How about this:

update yourTable
set (your XML column).modify('insert <processingData id="guid" someAttrib="x" /> as last into /someData[1]')
where .......

That should do it.

For more details on how to deal with XML in SQL Server 2005 and up, I keep going back to this article at 15 seconds which shows really nicely how to insert, modify, and delete XML fragments inside your SQL server fields, using XML DML statements.

Marc