Compare 2 XML Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 08 May 2008 05:52 GMT

Hi all:

I have a table with 2 XML type columns of the same structure. How do I
compare these 2 columns and return the difference? For example, here are some
sample data in the two xml columns:

xmlcol1 xmlcol2
<r1> <r1>
<v1>1</v1> <v1>2</v1>
<v2>Test1</v2> <v2>Test1</v2>
<v3>true</v3> <v3>false</v3>
</r1> </r1>

The expected result is:

<r1>
<v1>2</v1>
<v3>false</v3>
</r1>

Thanks in advance.

Reply to this Message

Joe Fawcett - 08 May 2008 08:06 GMT

> Hi all:
>
[quoted text clipped - 18 lines]
>
> Thanks in advance.

Unless you know the structure well in advance that's pretty hard to do from
within SQL Server. There is a tool, called XmlDiff that performs a file diff
but based on the XML InfoSet.

If the structure is fixed then an XSLT could compare them as well.

Signature

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

Reply to this Message

Tom - 08 May 2008 13:10 GMT

Thanks for your response, Joe. Yes, the structure is fixed and it is actually
a row in a table. Would you have some examples of using either XSLT or XML
Diff tool inside of SQL Server?

> > Hi all:
> >
[quoted text clipped - 24 lines]
>
> If the structure is fixed then an XSLT could compare them as well.

Reply to this Message

Bob Beauchemin - 08 May 2008 16:01 GMT

Here's a pointer to Dan Sullivan's blog entry on (and .NET function for)
comparing two XML instances using digital signature of XML canonical form.
http://www.pluralsight.com/blogs/dan/archive/2006/09/01/36829.aspx

Cheers,
Bob Beauchemin
SQLskills

> Thanks for your response, Joe. Yes, the structure is fixed and it is
> actually
[quoted text clipped - 32 lines]
>>
>> If the structure is fixed then an XSLT could compare them as well.

Reply to this Message

Tom - 08 May 2008 17:31 GMT

Thanks, Bob. That's an excellent article to compare if two XML message is
equal. In my case, I already know they are different, and I just want to get
the elements that are changed.

Is it efficient to traverse through the whole XML tree and compare element
by element? Is there an easy way to do this in XPath query? Please help.

> Here's a pointer to Dan Sullivan's blog entry on (and .NET function for)
> comparing two XML instances using digital signature of XML canonical form.
[quoted text clipped - 40 lines]
> >>
> >> If the structure is fixed then an XSLT could compare them as well.

Reply to this Message

Bob Beauchemin - 08 May 2008 21:56 GMT

I think you're better off extracting the XML to files and running the
XmlDiff tool then. That's what its good at.

Cheers,
Bob Beauchemin
SQLskills

> Thanks, Bob. That's an excellent article to compare if two XML message is
> equal. In my case, I already know they are different, and I just want to
[quoted text clipped - 52 lines]
>> >>
>> >> If the structure is fixed then an XSLT could compare them as well.

Reply to this Message

Bob - 29 Jun 2008 23:45 GMT

How about shredding the XML and using the database to do the compare?
SQL2005 demo below:

USE tempdb

DROP TABLE #tmp
CREATE TABLE #tmp( col1 XML, col2 XML )

DECLARE @xml1 XML
DECLARE @xml2 XML

SET @xml1 = '<r1>
<v1>1</v1>
<v2>Test1</v2>
<v3>true</v3>
</r1>'

SET @xml2 = '<r1>
<v1>2</v1>
<v2>Test1</v2>
<v3>false</v3>
</r1>'

INSERT INTO #tmp VALUES( @xml1, @xml2 )
GO

SELECT *
FROM
    (
    SELECT *
    FROM
        (
        SELECT *
        FROM
            (
            SELECT
                col1.v.value('(v1/text())[1]', 'VARCHAR(20)' ) as v1,
                col1.v.value('(v2/text())[1]', 'VARCHAR(20)' ) as v2,
                col1.v.value('(v3/text())[1]', 'VARCHAR(20)' ) as v3
            FROM #tmp
                CROSS APPLY col2.nodes('/r1') as col1(v)
            ) col2
        UNPIVOT ( xvalue for source_column In ( v1, v2, v3 ) ) upvt

        EXCEPT

        SELECT *
        FROM
            (
            SELECT
                col1.v.value('(v1/text())[1]', 'VARCHAR(20)' ) as v1,
                col1.v.value('(v2/text())[1]', 'VARCHAR(20)' ) as v2,
                col1.v.value('(v3/text())[1]', 'VARCHAR(20)' ) as v3
            FROM #tmp
                CROSS APPLY col1.nodes('/r1') as col1(v)
            ) xcol1
        UNPIVOT ( xvalue for source_column In ( v1, v2, v3 ) ) upvt
        ) diff
    ) x
PIVOT ( MAX( xvalue ) For source_column In ( v1, v2, v3 ) ) pvt
FOR XML RAW(''), ELEMENTS, ROOT('r1')

HTH
wBob

> Hi all:
>
[quoted text clipped - 17 lines]
>
> Thanks in advance.

Reply to this Message