We help IT Professionals succeed at work.

How do I merge two XML documents in SQL Server 2005

Medium Priority
299 Views
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
<extradata>
   <SystemID>1234</SystemID>
   <Phone>555-555-5555</Phone>
   <URL>www.mydomain.com</URL>
</extradata>

Line Item #2 - Hidden
<extradata>
  <SystemID>9876</SystemID>
  <Phone>555-555-5555</Phone>
  <URL>www.mydomain.com</URL>
</extradata>

So when the customer updates their URL to www.mydomain2.com, 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.
Comment
Watch Question

James MurrellQA Product Specialist
CERTIFIED EXPERT

Commented:
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
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.