How do I merge two XML documents in SQL Server 2005

I have a system that stores all extra information for an order's line item in an XML column.  The xml column is untyped, and while the root element is static, all child elements are dynamic.

The system now has a new type of line item that, upon ordering, creates a hidden line item that is used for a secondary product that is essentially a freebie.  Everything is working fine during the create.  The problem is that if a customer edits their order before it is fulfilled, I need the updated information to be merged into the hidden line item as well.

The problem I've run into is due to the xml column having dynamic elements.  I want to make this process run as a trigger on the line item table.  I can't extract the list of child elements using t-sql, so I then can't compare them to the hidden line item, and update or insert as necessary.  If the child elements had been staticly named (ie <Field>), I can easily use the .nodes method.  I'm just having trouble with the dynamic element names.

Here's a sample:

The "extradata" element is always present (static).  The "SystemID" element is most likely there.  But all other elements are dynamic - in the example below, there is "Phone" and "URL", but on another line item it might have "Size" and "Color".  

Line Item #1 - Customer Visable

Line Item #2 - Hidden

So when the customer updates their URL to, the trigger will run, grab the new xml document from inserted, and merge the data (except the SystemID element) into the hidden line item.  

Unfortunately, the system has been running too long for me to refactor the xml document to make the elements static.  The system admin doesn't want to enable .net assemblies on the SQL Server, so I've either got to get this working in t-sql, or scrap the trigger altogether and go to timed event or something.

Thanks in advance, and if I'm not being clear, just ask.
Who is Participating?
simsystemAuthor Commented:
I was able to work through this using a combination of XML functions and String functions.  Not the most beautiful solution, nor the most efficient.  And in SQL2005, it only allows updating, not inserting.  SQL2008 includes support that allows me to do the insertion of missing nodes.

Not a great solution, but it gets the job done.
James MurrellProduct SpecialistCommented:
been looking into this: lots of searching and book flicking i personally think timed event might be way to go... if someone know better I hope they post here
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.