2005 xml equality

There is not equality comparison for xml data type. You cannot cast xml to
text or ntext, but you can cast to varbinary(max), varchar(max), and
nvarchar(max).
A couple of routes you can take (I'm sure there are other ways as well):
1. Assuming the incoming xml is identical in structure (whitespace doesn't
count) to the existing xml value , you could cast the incoming value and
existing value to varbinary(max) and compare.
2. You could iterate through the xml and compare, although this is more
complicated to implement and slower as the size of the xml value increases.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:e$kf%23YvwHHA.5008@.TK2MSFTNGP05.phx.gbl...
> Is there any (perhaps roundabout) mechanism for testing xml columns
> for equality?
> Specifically, as part of a bulk update (i.e. into a staging table that
> is then migrated) I want to short-circuit any records whose xml hasn't
> actually changed, since I have audit requirements and I don't want to
> fill in the audit every time a record is present in an extract - only
> when it has actually changed.
> Any ideas? I have tried a few approaches like extracting as ntext or
> some of the checksum functions, but not with any success. CLR perhaps?
> Marc
>
>
Thanks Peter - I'll give the varbinary(max)/varchar(max)/nvarchar(max)
route a go in the morning. I'm confident that this will make
everything work - much appreciated.
Marc