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.
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.