title: How can I use sp_xml_preparedocument on result of NTEXT query in SQL 2000?
body: <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>EXEC sp_xml_preparedocument @XmlHandle output, (SELECT ProfileXml FROM ImportProfile WHERE ProfileId = 1)
  </code></pre>