May 23, 2008 at 3:28 am
Does anyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?
[Code]
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2
[/code]
I tried this works in MSSQL 2005,
but not MSSQL 2000.
Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Is this true (from BOL)?
[Code]
In comparing these column values,
if any of the columns to be compared are of type text, ntext, or image,
FOR XML assumes that values are different
(although they may be the same because Microsoft® SQL Server™ 2000 does not support comparing large objects);
and elements are added to the result for each row selected.
[/code]
May 30, 2008 at 2:51 am
Please, explain in more details what your business requirement is (rather than telling us *how* you want to solve the problem).
There's also a serious flaw in your logic. For instance if the subquery returns more than one row you'll get an error. Try this:
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Insert into #t2
Values('This is another test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2
Perhaps you meant something like this?
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Insert into #t2
Values('This is another test')
Select * from #t1
Select * from #t2
Select*
from#t1
inner join #t2
on#t1.[c2] LIKE #t2.[c2]
drop table #t1
drop table #t2
(Works as expected in both versions.)
NB: the last query will only return rows where the values are exactly the same. Is that what you need?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 30, 2008 at 5:58 am
Great, thanks. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply