removing root nodes from xmls in t-sql and merging them

Question

I'm actually planning on the best way to merge xml data from within the SQL database.

I have a table that contains an xml datatype. Inside it are xml's with similar schemas. I wanted to merge, let's say, two xmls and they both contain a <Custom></Custom> root on both ends.

What is the best way to do this?

First xml:

<Custom>
  <Data1>
   <ID>23423</ID>
   <Row1>value</Row1>
  </Data1>
  <Data1>
   <ID>57568</ID>
   <Row1>value</Row1>
  </Data1>
  <Data1>
   <ID>56756</ID>
   <Row1>value</Row1>
  </Data1>
 </Custom>

Second xml:

<Custom>
  <Data1>
   <ID>6575</ID>
   <Row1>value</Row1>
  </Data1>
  <Data1>
   <ID>8345</ID>
   <Row1>value</Row1>
  </Data1>
  <Data1>
   <ID>5666</ID>
   <Row1>value</Row1>
  </Data1>
 </Custom>

Please don't mind the ID's, the actual data is in GUID so merging won't be as difficult.

Answer 1

your problem description is vague and incomplete for what you are trying to do but here it goes

it sounds like you are saying i have two xml data sets with the same root nodes but nothing else (potentially) in common?

so if you want a system to compare the differences between them i'd look at what's available in terms of .net and open source stuff. this has been thought of before.

merging is a very complicated thing. if both sides have different values how do you decide who to "merge to"? it is not as straightforward as you might think.

the bottom line is. you need to explain your exact scenario in alot more detail to have a real response but lacking that, i would advise you to define what exactly you are looking to do... merge with user intervention / without / sometimes / in a more clear problem description and you will get better results.

this is my first post, i hope it is useful