Purchase XML in SQL SERVER

Question

Purchase XML in SQL SERVER

Navigation

#1 by (0 votes)

0

I have two xml and I want to see which fields were modified, however the result is not formatting them as they were entered:

DECLARE @xml1 xml ='<id>41</id><codigo>00054</codigo>';
DECLARE @xml2 xml ='<id>41</id><codigo>00054</codigo><idequipo>1</idequipo>';

SELECT * 
    FROM (
        SELECT T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
        T.N.value('.', 'nvarchar(100)') as Value
        FROM @xml2.nodes('/*') AS T(N)
        EXCEPT
        SELECT  T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
        T.N.value('.', 'nvarchar(100)') as Value
        FROM @xml1.nodes('/*') AS T(N)
        ) AS x FOR XML PATH

Your result is:

<row><NodeName>idequipo</NodeName><Value>1</Value></row>

And I want it:

<idequipo>1</idequipo>

Any suggestions?

sql sql-server

asked by millan2993 06.07.2018 в 05:51

source

1 answer

Stored procedure with Declare and Set phpmyadmin How to do a procedure stored in mysql that inserts the id from one table to another?

score 0 · Accepted Answer

How I can not think of now how to "map" the value of one of the columns as a node and the other as a value, what I can suggest is to directly construct each node / value in the following way:

DECLARE @xml1 xml ='<id>41</id><codigo>00054</codigo>';
DECLARE @xml2 xml ='<id>41</id><codigo>00054</codigo><idequipo>1</idequipo>';

SELECT REPLACE(REPLACE(STUFF(
    (
    SELECT ' <'+NodeName + '>' + Value + '</'+NodeName + '>'
        FROM (
            SELECT T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
            T.N.value('.', 'nvarchar(100)') as Value
            FROM @xml2.nodes('/*') AS T(N)
            EXCEPT
            SELECT  T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
            T.N.value('.', 'nvarchar(100)') as Value
            FROM @xml1.nodes('/*') AS T(N)
            ) AS x FOR XML PATH ('')
    ), 1, 1, ''), '&lt;', '<'), '&gt;', '>')

And now yes, the expected output:

<idequipo>1</idequipo>

With ' <'+NodeName + '>' + Value + '<'+NodeName + '>' we create the node in an "ad-hoc" way and then using STUFF() we concatenate everything to generate a single variable, all we have left is to "des-escape" the characters < and > .