Compare Xml data in SQL

Question

I have two tables with same NVARCHAR field that really contains XML data. in some cases this really-XML-field is really same as one row in other table but differs in attributes order and therefor string comparison does not return the correct result!!!

and to determining the same XML fields ,I need to have a comparison like:

  cast('<root><book b="" c="" a=""/></root>' as XML) 
= cast('<root><book a="" b="" c=""/></root>' as XML)

but I get this Err Msg:

The XML data type cannot be compared or sorted, except when using the IS NULL operator.

then what is the best solution to determine the same XML without re-casting them to NVARCHAR?

Comparing XML not just syntactically, but based on the actual meaning of the XML text representation is actually quite a tricky business. I highly doubt you'll be able to do this easily within SQL Server - most likely, you'd have to use an app that can parse and interpret the XML to find out if it's the same or different. A simple textual comparison will not be easy or sufficient.... — marc_s
– marc_s, Commented Sep 15, 2011 at 10:13
Please don't try to roll your own xml parser in sql. Use an established one that can be leveraged from your language of choice. — Ryan Gates
– Ryan Gates, Commented Oct 24, 2012 at 16:10

Wil · Accepted Answer · 2011-09-17 16:30:51Z

2

Why cast it at all? Just plug them into an XML column in a temp table and run Xquery to compare them to the other table. EDIT: Included example of the comparison. There are many, many ways to run the query against the XML to get the rows that are the same - exactly how that query is written is going to depend on preference, requirements, etc. I went with a simple group by/count, but a self join could be used, WHERE EXISTS against the columns that are being searched for duplicates, you name it.

CREATE TABLE #Test (SomeXML NVARCHAR(MAX))
CREATE TABLE #XML (SomeXML XML)

INSERT #Test (SomeXML)
VALUES('<root><book b="b" c="c" a="a"/></root>')
    ,('<root><book a="a" b="b" c="c"/></root>')

INSERT #XML (SomeXML)

SELECT SomeXML FROM #Test;

WITH XMLCompare (a,b,c)
AS
(
SELECT 
    x.c.value('@a[1]','char(1)') AS a
    ,x.c.value('@b[1]','char(1)') AS b 
    ,x.c.value('@c[1]','char(1)') AS c  
FROM #XML
CROSS APPLY SomeXMl.nodes('/root/book') X(C)
)

SELECT 
    a
    ,b
    ,c
FROM XMLCompare as a
GROUP BY
    a
    ,b
    ,c
HAVING COUNT(*) >1

edited Sep 17, 2011 at 16:30

answered Sep 15, 2011 at 16:22

Wil

4,3101 gold badge19 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Wil Over a year ago

@ARZ, XQuery. Posting a revision with comparison.

ARZ Over a year ago

thanks 4 your reply. but how to compare the entire body of XMLs directly? (without comparing all attributes Individually)

Wil Over a year ago

To my knowledge, no there is no way to do so. All solutions I've seen to determine if two XML fragments are equivalent simply break down the XML fragments and compare the elements directly.

serendipity07 Over a year ago

I am wondering if it is possible to sort the attributes and elements of both sides of the comparison.

Collectives™ on Stack Overflow

Compare Xml data in SQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related