xml representation in sql server 2000 stored procedure URGENT

[spaliwal]

Hi Gurus,

I am using following code segment in stored procedure -

DECLARE @hdoc int
DECLARE @doc nvarchar(4000)

select @doc = detailXML from Tab1 where col1 = val1

exec sp_xml_preparedocument @hdoc OUTPUT,@doc

select * from OPENXML(@hdoc,'Root/Customer',1)
with (CustomerID varchar(10),
CustomerName varchar(20))
exec sp_xml_removedocument @hdoc

but my field detailXML.Tab1 is of text datatype and of variable length , there is a limitation of stored proc local variable can not be defined as text datatype. As sp_xml_preparedocument support text as a input document, there must be some way out to take input doc (@doc) as text datatype.Is there any workaround or service pack or diffent way to do the same,Need a help urgently.

TIA,

Sheilesh

 

[pgtek]

hi
read this i might help you

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_xml_267o.asp

pgtek