[Apologies for the cross-post]
Hi,
I have the following query in a stored procedure, where @.In_IDs is of type
XML:
SELECT Images.IsCompressed,
Images._Timestamp
FROM
Images
CROSS APPLY
@.In_IDs.nodes('//id') AS T(nref)
WHERE
Images.ID_Adjacency = nref.value('.', 'int')
However, sometimes I'm sending a lot of IDs (possibly a few thousand) and
the resulting XML document seems to become truncated. i.e. if I send 1,000,
I will get back 950 records. What I would like to do is pass in an NTEXT
field and convert this to XML in order to do the join on the full set. Any
ideas how I do this?
Thanks
RobinHello Robin,
> However, sometimes I'm sending a lot of IDs (possibly a few thousand)
> and the resulting XML document seems to become truncated. i.e. if I
> send 1,000, I will get back 950 records. What I would like to do is
> pass in an NTEXT field and convert this to XML in order to do the join
> on the full set. Any ideas how I do this?
Here's one way to do that.
use scratch
go
create table dbo.objects(id int,descr nvarchar(200))
create table dbo.ids(list ntext)
go
insert into dbo.objects values (1,'apple')
insert into dbo.objects values (2,'banana')
insert into dbo.objects values (3,'cherry')
insert into dbo.objects values (4,'durian')
go
insert into dbo.ids(list) values ('<ids><id>1</id><id>2</id><id>3</id><id>4<
/id></ids>')
go
declare @.x xml
select @.x = list from dbo.ids
;with l(id) as (select t.c.value('.','int')
from @.x.nodes('//id') as t(c))
select l.id,o.descr from l join dbo.objects o on l.id = o.id
go
drop table dbo.ids
drop table dbo.objects
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent, thanks for your response. I think some misunderstanding here
though with your answer - I can't quite see how to make that work. I have
the following (below). My problem is with the truncation of @.In_IDs (the
XML parameter). I want to replace it with an NTEXT parameter and then
somehow prepare it as an XML document in order to perform the query. I'm
passing in an XML list of ID's and want to return the record for each of
those IDs but I think the limit on the size of the XML document prevents the
code below from working when the number of ID's is largeish.
Robin
CREATEPROCEDURE [dbo].[Image_Get_Set_Details]
@.In_IDs XML /* The IDs of the image whose details we want to fetch
*/
/*
Procedure expects XML in the following format:
<query>
<id>1023</id>
<id>1024</id>
<id>1025</id>
</query>
*/
AS
SELECT Images.ID_Adjacency,
Images.IsCompressed,
Images._Timestamp
FROM
Images
CROSS APPLY
@.In_IDs.nodes('//id') AS T(nref)
WHERE
Images.ID_Adjacency = nref.value('.', 'int')|||Hello Robin,
The XML DataType supports instances up to two 2gb so that seems unlikely.
NTEXT does the same thing, but it is depreciated so I'd avoid using it. Here
's
an example that does essentially the same work your looking for. It works
for me for lists from 100 to 1000000 elements.
use scratch
go
create table dbo.test(id int identity(1,1) primary key clustered,v int)
go
set nocount on
declare @.l int
set @.l = 100000
while @.l > 0
begin
insert into dbo.test values (@.l)
set @.l = @.l-1
end
go
create procedure dbo.GetVs(@.tlist nvarchar(max))
as begin
set nocount on
declare @.list xml
set @.list = @.tlist
select @.list.value('count(//id)','int') as [count],datalength(@.list) as
[size]
select t.c.value('.','int') as ID,t1.v
from dbo.test t1
cross apply @.list.nodes('//id') as t(c)
where t1.id = t.c.value('.','int')
end
go
declare @.olist nvarchar(max)
select @.olist = convert(nvarchar(max),( select v as id from dbo.test order
by v for xml path(''),root('query'),type))
exec dbo.GetVs @.olist
go
drop table dbo.test
drop proc dbo.GetVs
go
Note that converting the list to nvarchar(max) isn't required as all. This
also works:
use scratch
go
create table dbo.test(id int identity(1,1) primary key clustered,v int)
go
set nocount on
declare @.l int
set @.l = 100000
while @.l > 0
begin
insert into dbo.test values (@.l)
set @.l = @.l-1
end
go
create procedure dbo.GetVs(@.list xml)
as begin
set nocount on
select @.list.value('count(//id)','int') as [count],datalength(@.list) as
[size]
select t.c.value('.','int') as ID,t1.v
from dbo.test t1
cross apply @.list.nodes('//id') as t(c)
where t1.id = t.c.value('.','int')
end
go
declare @.list xml
select @.list = ( select v as id from dbo.test order by v for xml path(''),ro
ot('query'),type)
exec dbo.GetVs @.list
go
drop table dbo.test
drop proc dbo.GetVs
go
What you might want to do is test that you're getting all the nodes in the
list that you think you are, as I do with the XQuery count select.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent. It turns out I had a bug somewhere which lead me to believe
there was a limit on XML parameter size, totally unrelated to my sproc.
Sorry to have wasted your time.
Robin
