How do I merge two XML documents in SQL Server 2005

Posted on 2009-02-12
Last Modified: 2012-05-06
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.
Question by:simsystem
    LVL 31

    Expert Comment

    by:James Murrell
    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

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now