Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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
<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.
0
simsystem
Asked:
simsystem
1 Solution
 
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
0
 
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now