find the field name when compare two xml variables in sql?

Question

I have following query to compare two xml variables and find the difference between two:

DECLARE 
      @oldXML NVARCHAR(MAX)
    , @newXML NVARCHAR(MAX)

SELECT 
      @oldXML = '<root><row USER_KEY="1" USER_NAME="test" USER_ID="12345" USER_STATUS=" " USER_GENDER="F" /></root>' 
    , @newXML = '<root><row USER_KEY="1" USER_NAME="test" USER_ID="00000" USER_STATUS=" " USER_GENDER="F" /></root>'

;WITH cte AS 
(
    SELECT id, t.rn, token = SUBSTRING(name, 1, CHARINDEX('" ', t.name) - 1)
    FROM (
        SELECT 
              name = 
                SUBSTRING(
                      t.string
                    , number + 2
                    , ABS(CHARINDEX('="', t.string, number + 1) - number - 1))
            , rn = ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY (SELECT 1))
            , id
        FROM (
            SELECT id = 1, string = @newXML

            UNION ALL

            SELECT id = 2, @oldXML
        ) t
        CROSS JOIN [master].dbo.spt_values n
        WHERE [type] = 'p'
            AND number <= LEN(t.string) - 1
            AND SUBSTRING(t.string, number, 2) = '="'
    ) t
)
SELECT t2.token
FROM (
    SELECT * 
    FROM cte 
    WHERE id = 1
) t1
LEFT JOIN (
    SELECT * 
    FROM cte 
    WHERE id = 2
) t2 ON t1.rn = t2.rn AND t1.token != t2.token
WHERE t2.token IS NOT NULL

This is giving me the different fields in oldxml and newxml. In the above example out put is 12345 which is USER_ID is the only field different. But here i also need the name of which field/tag is different. In this case I also need to get "USER_ID" as output.

Thanks

score 3 · Accepted Answer · edited May 23 '17 at 12:19

Why not treat the XML as actual XML. Sample below. The comparison code was taken from here and modified:

DECLARE 
      @oldXML XML
    , @newXML XML

SELECT 
      @oldXML = '<root><row USER_KEY="1" USER_NAME="test" USER_ID="12345" USER_STATUS=" " USER_GENDER="F" /></root>' 
    , @newXML = '<root><row USER_KEY="1" USER_NAME="test" USER_ID="00000" USER_STATUS=" " USER_GENDER="F" /></root>'

;with XML1 as
(
  select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @oldXML.nodes('/root/row/@*') as T(N)
),
XML2 as
(
  select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @newXML.nodes('/root/row/@*') as T(N)
)
select coalesce(XML1.NodeName, XML2.NodeName) as NodeName, 
       XML1.Value as Value1, 
       XML2.Value as Value2
from XML1
  full outer join XML2
    on XML1.NodeName = XML2.NodeName
where coalesce(XML1.Value, '') <> coalesce(XML2.Value, '')

This will return:

NodeName    Value1  Value2
USER_ID     12345   00000

find the field name when compare two xml variables in sql?

1 Answers1