How can I use sp_xml_preparedocument as a result of an NTEXT query in SQL 2000?

Question

I know that NTEXT is leaving and that there are bigger problems with best practice (for example, storing XML in an NTEXT column), but I have a table containing XML from which I need to pull out the attribute value. This should be easy to do with sp_xml_preparedocument, but it gets more complicated because you cannot declare a local variable of type NTEXT, and I cannot figure out how to use an expression to indicate the XML text passed to the function. I can do it like this in SQL 2005 due to XML or VARCHAR (MAX) data types, but what can I do for SQL 2000?

DECLARE @XmlHandle int DECLARE @ProfileXml xml SELECT @ProfileXml = ProfileXml FROM ImportProfile WHERE ProfileId = 1 EXEC sp_xml_preparedocument @XmlHandle output, @ProfileXml -- Pluck the Folder TemplateId out of the FldTemplateId XML attribute. SELECT FolderTemplateId FROM OPENXML( @XmlHandle, '/ImportProfile', 1) WITH( FolderTemplateId int '@FldTemplateId' ) EXEC sp_xml_removedocument @XmlHandle 

The only thing I can think of for SQL 2000 is to use varchar (8000). Is there no way to use an expression like the following?

 EXEC sp_xml_preparedocument @XmlHandle output, (SELECT ProfileXml FROM ImportProfile WHERE ProfileId = 1) 

Answer 1

Great question .. but without a solution

Thoughts:

• You cannot wrap a SELECT call in UDF (to create a dummy ntext local var view)
• You cannot wrap the sp_xml_preparedocument call in scalar UDF (use in SELECT) because you cannot call extended stored procedures
• You cannot combine a call to run dynamically because you will encounter string constraints and error problems.
• Same as using OPENQUERY
• textptr + READTEXT cannot be added as a parameter in sp_xml_preparedocument

So why does sp_xml_preparedocument accept ntext as a data type?