Re: Convert NTEXT to XML for use in following query.

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(''),root('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/


.

Relevant Pages

  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... I got an xml routine from the Sql Server NG where I can ... delimiters in the string. ... Declare @iDoc Int ...
    (microsoft.public.sqlserver.xml)
  • Re: SQL StoredProc Help
    ... Hai Boss Hog, ... The XML i use is like this.. ... DECLARE @IDOC INT ...
    (microsoft.public.sqlserver.xml)
  • Re: SQL StoredProc Help
    ... if i have understand you need to pass a xml stream to your stored proc ?! ... @USER_ID int, ... Declare @CORP_PRODUCT_COUNT int ...
    (microsoft.public.sqlserver.xml)
  • Re: [Luxasm-devel] luxasm/documents/Xlib
    ... and your html Xlib doc conversion. ... Like I say, I only didn't bother that last time, just ... whether to bother with XML or not...it's a good idea in some ... could also be alternative "lists" that detail things like Xlib ...
    (alt.lang.asm)
Loading