Merging XML in an SQL Server

Question

Let's say I have the following two pieces 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 datatype, additionally this XML column is linked to a schema that describes the format of expected xml e.g

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

Within a stored procedure / function I would like to merge 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 piece of xml we have taken items:

a, b

from the second piece of xml we have taken items:

c, d, e  

Notice that the two pieces of XML have a common item with a key of:

c

In this scenario the value from xml 2 should be used in the merged xml (discarding the value from xml 1). Another case is that the XML 1 or 2 could be NULL therefore the merge process should handle this and simply return the other. Or both could be NULL in which case NULL is returned.

As an aside, in our current implementation we are returning both XML documents from the DB and doing the merge in code. However we would prefer to have this merge done within the DB as multiple unrelated processes are calling this proc.

Answer 1

Use:

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 t.c.value('@key', 'nvarchar(max)') [a], t.c.value('.', 'nvarchar(max)') [b]
        from @x1.nodes('/*/item') t(c)
    )t1
    full join
    (
        select t.c.value('@key', 'nvarchar(max)') [a], t.c.value('.', 'nvarchar(max)') [b]
        from @x2.nodes('/*/item') t(c)
    )t2 on t2.a = t1.a
)t
for xml path('item'), root('pairs')

Output:

<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 t.c.value('@key', 'nvarchar(max)') [a], t.c.value('.', 'nvarchar(max)') [b]
    from @t1 ta
    cross apply ta.data.nodes('/*/item') t(c)
)t1
full join
(
    select t.c.value('@key', 'nvarchar(max)') [a], t.c.value('.', '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

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