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

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hello 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/


.

Relevant Pages

  • OPENXML INSERT/UPDATE and NTEXT
    ... I'm having problems inserting/updating a NTEXT field using OPENXML. ... declare @xml nvarchar; ... EXEC sp_xml_preparedocument @doc OUTPUT, @xml ...
    (microsoft.public.sqlserver.programming)
  • Re: retrieving XML data from table
    ... I assume that you would like to use FOR XML to reconstruct the XML and ... reinsert it into the ntext field. ... That code can then be called from within the SQL Server ... > I can retrieve xml data from xData field for each record with openxml ...
    (microsoft.public.sqlserver.xml)
  • SQL Select from NTEXT field with XML Dataset
    ... I'm trying to write a select statement in TSQL (SQL Server 2000) against an ... NTEXT field that holds an XML dataset. ... It returns text with the XML tags in place. ...
    (microsoft.public.sqlserver.xml)
  • RE: XML documentation file name
    ... MSDN document on VB.NET Project Designer says "The Generate XML document ... XML documentation is automatically emitted into an XML ... Microsoft Online Community Support ...
    (microsoft.public.dotnet.languages.vb)
  • Re: XMLTextReader reading too many characters
    ... It was a very simple process to delete the extra tag. ... You do know about XML ... status isn't indicative of being capable of editing an XML document. ... If you claim there is a problem with XmlTextReader and an allegedly ...
    (microsoft.public.dotnet.xml)