Compare XML in two tables

Question

Compare XML in two tables

caippers

SSC-Addicted

Points: 472
More actions
January 6, 2014 at 8:13 am

#281755

I'm trying to compare the data in two tables that store XML. I've tried a few different things. I tried to convert the xml to int and the Binary_Checksum.
With New_CodeXML as(
Select
binary_checksum(new.PracticeOfficeXML) as PracticeOfficeXML,
binary_checksum(new.FacilityXML) as FacilityXML
from ODS1Stage.show.SOLRProvider_nandita new),
Old_codeXML as (
Select
binary_checksum(newbu.PracticeOfficeXML)as PracticeOfficeXML,
binary_checksum(newbu.FacilityXML) as FacilityXML
from DataBackup.show.solrprovider_12_30_2013 newbu)
Select 'New Code' as Code,x.* from (Select * from New_CodeXML except Select * from Old_CodeXML) x
Union All
Select 'Old Code' as Code,y.* from (Select * from Old_CodeXML except Select * from New_CodeXML) y
I get the following error.
Msg 8184, Level 16, State 1, Line 2
Error in binarychecksum. There are no comparable columns in the binarychecksum input.
This same query works on nonXML files.
Any suggestions on how or what I should convert the XML to?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

KS Hall of Fame Points: 3323 More actions · Answer 1

CHECKSUM & BINARY_CHECKSUM cannot accept text, ntext, image, cursor, xml, and noncomparable common language runtime (CLR) user-defined types. Try converting the XML data-type to VARCHAR.

With New_CodeXML as (

Select

binary_checksum(convert(varchar(max),new.PracticeOfficeXML)) as PracticeOfficeXML,

binary_checksum(convert(varchar(max),new.FacilityXML)) as FacilityXML

from ODS1Stage.show.SOLRProvider_nandita new),

Old_codeXML as (

Select

binary_checksum(convert(varchar(max),newbu.PracticeOfficeXML))as PracticeOfficeXML,

binary_checksum(convert(varchar(max),newbu.FacilityXML)) as FacilityXML

from DataBackup.show.solrprovider_12_30_2013 newbu)

Select 'New Code' as Code,x.* from (Select * from New_CodeXML except Select * from Old_CodeXML) x

Union All

Select 'Old Code' as Code,y.* from (Select * from Old_CodeXML except Select * from New_CodeXML) y

caippers SSC-Addicted Points: 472 More actions · Answer 2

caippers

SSC-Addicted

Points: 472

January 6, 2014 at 8:35 am

#1677775

That worked perfect.

I appreciate it!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Better yet, stop storing denormalized data in the form of XML in your database (never mind storing it twice!). 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

caippers SSC-Addicted Points: 472 More actions · Answer 4

I'm sure my company would love me saying that on day 3....

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

caippers (1/6/2014)
I'm sure my company would love me saying that on day 3....

You mean that you didn't say it already at day 1?:-)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/