How can I use sp_xml_preparedocument on result of NTEXT query in SQL 2000?
Great question.. but no solution
Thoughts:
- You can't wrap the SELECT call in a UDF (to create a kind of dummy ntext local var)
- You can't wrap the
sp_xml_preparedocument
call in a scalar UDF (to use in SELECT) because you can't call extended stored procs - You can't concatenate a call to run dynamically because you'll hit string limits and scop issues
- Ditto a self call using OPENQUERY
- textptr + READTEXT can't be added as a parameter to
sp_xml_preparedocument
So why does sp_xml_preparedocument
take ntext as a datatype?
flipdoubt
Full-stack .NET and Javascript generalist by necessity.
Updated on June 04, 2022Comments
-
flipdoubt about 2 years
I know NTEXT is going away and that there are larger best-practices issues here (like storing XML in an NTEXT column), but I have a table containing XML from which I need to pluck a attribute value. This should be easy to do using sp_xml_preparedocument but is made more tricky by the fact that you cannot declare a local variable of type NTEXT and I cannot figure out how to use an expression to specify the XML text passed to the function. I can do it like this in SQL 2005 because of the XML or VARCHAR(MAX) datatypes, 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 come up with for SQL 2000 is to use varchar(8000). Is there really no way to use an expression like the following?
EXEC sp_xml_preparedocument @XmlHandle output, (SELECT ProfileXml FROM ImportProfile WHERE ProfileId = 1)