2

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?

ARZ
  • 2,461
  • 3
  • 34
  • 56
  • 2
    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 Sep 15 '11 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 Oct 24 '12 at 16:10

1 Answers1

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
Wil
  • 4,130
  • 1
  • 16
  • 15
  • @ARZ, XQuery. Posting a revision with comparison. – Wil Sep 17 '11 at 16:10
  • thanks 4 your reply. but how to compare the entire body of XMLs directly? (without comparing all attributes Individually) – ARZ Sep 18 '11 at 12:29
  • 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. – Wil Sep 18 '11 at 16:24
  • I am wondering if it is possible to sort the attributes and elements of both sides of the comparison. – zacharydl Nov 08 '13 at 05:03