raw - sql server select xml format

Fusionando XML en un Servidor SQL

Question

Digamos que tengo las siguientes dos piezas de XML en mi base de datos

<!-- 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>

Estos datos se almacenan en dos tablas separadas usando el tipo de datos XML , adicionalmente esta columna XML está vinculada a un esquema que describe el formato del xml esperado, por ejemplo

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

Dentro de un procedimiento / función almacenada me gustaría fusionar estas dos estructuras XML en lo siguiente:

<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>

Entonces, desde la primera pieza de xml hemos tomado elementos:

a, b

de la segunda pieza de xml hemos tomado elementos:

c, d, e  

Tenga en cuenta que las dos piezas de XML tienen un elemento común con una clave de:

c

En este escenario, el valor de xml 2 se debe usar en el xml fusionado (descartando el valor de xml 1). Otro caso es que XML 1 o 2 podrían ser NULL, por lo que el proceso de fusión debería manejar esto y simplemente devolver el otro. O ambos podrían ser NULL, en cuyo caso se devuelve NULL.

Como un aparte, en nuestra implementación actual estamos devolviendo ambos documentos XML desde el DB y haciendo la fusión en código. Sin embargo, preferiríamos hacer esta fusión dentro de la base de datos ya que varios procesos no relacionados están llamando a este proceso.

Answer 1

Ejemplo con tablas:

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

Answer 2

Utilizar:

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'')

Salida:

<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>

ACTUALIZAR:

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'')