Using text/ntext field in a stored procedure

[spaliwal]

Hi,

I have to retrive text/ntext field in a stored procedure , it contains XML patterns.I have to parse that xml in a tabular form. But problem is it is not possible to retrive text/ntext field in a local variable.Can anybody can suggest how to retrive text/ntext datatype field in a local variable. I am using sql server 2000.This is the code stored procedure.By this way imcomplete xml is coming out from the table.

Thanks,

Shailesh

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



ALTER PROCEDURE ParseDefaultCarePlanDetailXML(@x_DefaultCarePlanDetailOID varchar(32)) AS

declare @idoc int
declare @idoc1 int

declare @doc varchar(4000)


SELECT @doc = convert(varchar,DefaultCarePlanDetail.DetailXML)
FROM DefaultCarePlanDetail
WHERE OID = @x_DefaultCarePlanDetailOID


--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider

SELECT *
FROM OPENXML (@idoc, '/defaultCarePlan/drugCondition/problem/goal/intervention',1)
WITH (
DrugConditionTitle varchar(255) '../../../drugConditionTitle',
ProblemTitle varchar(255) '../../problemTitle',
GoalTitle varchar(255) '../goalTitle',
MonitorTitle varchar(255) '../monitor/monitorTitle',
InterventionTitle varchar(255) 'interventionTitle')
ORDER BY DrugConditionTitle, ProblemTitle, GoalTitle


EXEC sp_xml_removedocument @idoc




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO