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

2005 Inconsistent Results with VarBinary fields

Hello,
I seem to be encountering some inconsistent results with full text searching
against a combination of VarChar(max) and VarBinary(max) fields.
I have a table which has both a VarChar(max) field and a VarBinary(max)
field. The table is meant to either have text or an attachement (or both).
I have indexed both fields. When I do a particular search against just the
Varbinary field I get 16 results. When I do the same query, but with both
columns specified, I only get 8 results. Maybe there is a good reason for
this, but it seems like adding more fields to the search should not result
in fewer hits. And I have verified that the records it is missing truly
contain the word I am looking for. Below are the relevant queries.
Any help would be appreciated. Thanks!
Randall
-- ***** Table ****
create table dbo.z_FullTextChild (
FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
FullTextMainID uniqueidentifier not null,
LongText VarChar(max) null,
AttachmentFileName VarChar(256) null,
AttachmentFileType VarChar(15) null,
Attachment VarBinary(max),
constraint PK_FullTextChild primary key (FullTextChildID),
constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
cascade
)
go
-- ******** Full Text Index *********
create fulltext index on dbo.z_FullTextChild
(LongText, Attachment TYPE COLUMN AttachmentFileType )
key index PK_FullTextChild
on ftTracker
with CHANGE_TRACKING AUTO
go
-- ******* Query that yields 16 results ***************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******** Query that yields 8 results (only difference is in selected
columns) ******************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment, LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******* Select @.@.Version ***********
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
I have tried to repro this problem and am unable to do so. Can you perhaps
do this query?
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randall" <randall@.randall.com> wrote in message
news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I seem to be encountering some inconsistent results with full text
searching
> against a combination of VarChar(max) and VarBinary(max) fields.
> I have a table which has both a VarChar(max) field and a VarBinary(max)
> field. The table is meant to either have text or an attachement (or
both).
> I have indexed both fields. When I do a particular search against just
the
> Varbinary field I get 16 results. When I do the same query, but with both
> columns specified, I only get 8 results. Maybe there is a good reason for
> this, but it seems like adding more fields to the search should not result
> in fewer hits. And I have verified that the records it is missing truly
> contain the word I am looking for. Below are the relevant queries.
> Any help would be appreciated. Thanks!
> Randall
> -- ***** Table ****
> create table dbo.z_FullTextChild (
> FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
> FullTextMainID uniqueidentifier not null,
> LongText VarChar(max) null,
> AttachmentFileName VarChar(256) null,
> AttachmentFileType VarChar(15) null,
> Attachment VarBinary(max),
> constraint PK_FullTextChild primary key (FullTextChildID),
> constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
> references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
> cascade
> )
> go
> -- ******** Full Text Index *********
> create fulltext index on dbo.z_FullTextChild
> (LongText, Attachment TYPE COLUMN AttachmentFileType )
> key index PK_FullTextChild
> on ftTracker
> with CHANGE_TRACKING AUTO
> go
> -- ******* Query that yields 16 results ***************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
> on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******** Query that yields 8 results (only difference is in selected
> columns) ******************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment, LongText),
'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******* Select @.@.Version ***********
> Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005
18:22:46
> Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
> 5.1 (Build 2600: Service Pack 2)
>
>
|||Thanks for the reply.
That particular query yields no hits because currently none of the records
that have attachments have any LongText associated with them (although that
could change in the future).
Randall
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4eEKfuhFHA.1048@.tk2msftngp13.phx.gbl...
>I have tried to repro this problem and am unable to do so. Can you perhaps
> do this query?
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randall" <randall@.randall.com> wrote in message
> news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> searching
> both).
> the
> 'INFORMATION',
> 18:22:46
>