for xml returns data as image datatype in sql server 2000.
here you can see how to put text stored in image datatype into a ntext datatype:
http://groups.google.si/group/microsoft.public.sqlserver.programming/browse_thread/thread/1a40f3739f7a1b61/a062c097154de090?lnk=st&rnum=1&hl=sl#a062c097154de090
about the
"The FOR XML clause is not allowed in a INSERT statement." error
you can get the stored procedure result with a little help from a self-linked server:
use northwind
create procedure getXMLdata
as
select top 10 orderid, customerid from orders for XML auto,elements
go
if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go
create table #temp(col1 image)
insert into #temp
select * from openquery(loopback, 'exec northwind.dbo.getXMLdata')
select * from #temp
drop table #temp
go
drop proc getXMLdata
Go with the flow & have fun! Else fight the flow 
blog thingie: http://weblogs.sqlteam.com/mladenp
Help Needed: Capture result set of SP
Forum Locked
Posted - 11/10/2006 : 15:03:58
