Error issuing SQL xml output parameter?

Question

I have a SQL Server stored procedure with an output parameter of type xml . In my coldfusion code, I'm not sure which cfsqltype use for the xml variable. I tried using cf_sql_varchar in the following code:

 <cfprocparam cfsqltype="cf_sql_varchar" type="out" variable="result"> 

But I get the following error:

Implicit conversion from xml to varchar data type is not allowed. Use the CONVERT function to run this query.

Is there a way to handle the xml output option in coldfusion without changing the stored procedure to return varchar instead of xml?

Answer 1

Unfortunately, the answer to your question is no. The driver does not support reading the output of stored procedures such as XML data. If you need to use the XML data type as an output parameter, you must first convert the XML data type to VARCHAR or LONGVARCHAR into a stored procedure before it can be read.