XML federation in SQL Server

Question

Say I have the following two parts of XML in my database

<!-- XML 1 --> <pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value</item> <item key="c">xml 1 c value</item> </pairs> <!-- XML 2 --> <pairs> <item key="c">xml 2 c value</item> <item key="d">xml 2 d value</item> <item key="e">xml 1 e value</item> </pairs> 

This data is stored in two separate tables using the XML data type, in addition, this XML column is associated with a schema that describes the format of the expected xml, for example

 [PairData] [xml](CONTENT [foo].[Pairs]) NULL 

Inside the stored procedure / function, I would like to combine these two XML structures into the following:

 <pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value</item> <item key="c">xml 2 c value</item> <item key="d">xml 2 d value</item> <item key="e">xml 2 e value</item> </pairs> 

So, from the first part of xml we took the elements:

 a, b 

from the second xml fragment we took the elements:

 c, d, e 

Note that the two parts of XML share a key element:

 c 

In this case, the value from xml 2 should be used in the combined xml (discarding the value from xml 1). Another case is that XML 1 or 2 can be NULL, so the merge process should handle this and just return another. Or both can be NULL, in which case NULL is returned.

Aside, in our current implementation, we are returning both XML documents from the database and merging the code. However, we would prefer that this merge be done in the database, as several unrelated processes cause this process.

Answer 1

Using:

 declare @x1 xml ='<pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value</item> <item key="c">xml 1 c value</item> </pairs>' declare @x2 xml ='<pairs> <item key="c">xml 2 c value</item> <item key="d">xml 2 d value</item> <item key="e">xml 2 e value</item> </pairs>' select * from ( select isnull(t2.a, t1.a) [@key], isnull(t2.b, t1.b) [text()] from ( select tcvalue('@key', 'nvarchar(max)') [a], tcvalue('.', 'nvarchar(max)') [b] from @x1.nodes('/*/item') t(c) )t1 full join ( select tcvalue('@key', 'nvarchar(max)') [a], tcvalue('.', 'nvarchar(max)') [b] from @x2.nodes('/*/item') t(c) )t2 on t2.a = t1.a )t for xml path('item'), root('pairs') 

Conclusion:

 <pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value</item> <item key="c">xml 2 c value</item> <item key="d">xml 2 d value</item> <item key="e">xml 2 e value</item> </pairs> 

---

UPDATE:

 declare @x1 xml ='<pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value</item> <item key="c">xml 1 c value</item> </pairs>' declare @x2 xml ='<pairs> <item key="c">xml 2 c value</item> <item key="d">xml 2 d value</item> <item key="e">xml 2 e value</item> </pairs>' declare @t1 table(id int, data xml) insert @t1 values(1, @x1) declare @t2 table(id int, data xml) insert @t2 values(1, @x2) select isnull(t2.a, t1.a) [@key], isnull(t2.b, t1.b) [text()] from ( select tcvalue('@key', 'nvarchar(max)') [a], tcvalue('.', 'nvarchar(max)') [b] from @t1 ta cross apply ta.data.nodes('/*/item') t(c) )t1 full join ( select tcvalue('@key', 'nvarchar(max)') [a], tcvalue('.', 'nvarchar(max)') [b] from @t2 ta cross apply ta.data.nodes('/*/item') t(c) )t2 on t2.a = t1.a for xml path('item'), root('pairs') 

Answer 2

An example with tables:

 CREATE TABLE #x1 (row_key int, source_xml xml) CREATE TABLE #x2 (row_key int, source_xml xml) DECLARE @x1 xml = '<pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value</item> <item key="c">xml 1 c value</item> </pairs>' DECLARE @x2 xml = '<pairs> <item key="c">xml 2 c value</item> <item key="d">xml 2 d value</item> <item key="e">xml 1 e value</item> </pairs>' INSERT INTO #x1 VALUES (1, @x1) INSERT INTO #x2 VALUES (1, @x2) SELECT ISNULL(a.item_key, b.item_key) [@key] ,ISNULL(b.item_value, a.item_value) [text()] FROM ( SELECT b.value('@key', 'char(1)') item_key, b.value('.', 'nvarchar(100)') item_value FROM #x1 cross apply #x1.source_xml.nodes('./pairs/item') a(b) WHERE row_key = 1 ) a FULL JOIN ( SELECT b.value('@key', 'char(1)') item_key, b.value('.', 'nvarchar(100)') item_value FROM #x2 cross apply #x2.source_xml.nodes('pairs/item') a(b) WHERE row_key = 1 ) b ON a.item_key = b.item_key ORDER BY ISNULL(a.item_key, b.item_key) FOR XML PATH ('item'), ROOT ('pairs') DROP TABLE #x1 DROP TABLE #x2