CODEWITHSUNDEEP
sqlxmlt-sql
Martin Ongtangco
Martin Ongtangco

Reputation: 23505

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

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.

Upvotes: 0

Views: 961

Answers (2)

edhubbell
edhubbell

Reputation: 2258

Here's about how I'd do it:

DECLARE @First XML
SET @First = '<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>'

 DECLARE @Second XML
SET @Second = '<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>'

SET @First = (SELECT @First.query('/Custom/*'))
SET @Second = (SELECT @Second.query('/Custom/*'))

DECLARE @Merged XML
SET @Merged = (SELECT @First, @Second FOR XML RAW ('Custom'), ELEMENTS)

SELECT @Merged

Upvotes: 0

skelley
skelley

Reputation: 11

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.

Upvotes: 1

Related Questions