[thelist] Stored Proc to XML to Text File?

[Casey Crookston]

Hi,

I'm having some trouble writing out the results of a query to a text file.  The query in the stored proc uses FOR XML PATH, so that the result of the query is a properly formatted XML string.  (The query is such that it would not be valid without the FOR XML PATH statement, so returning a non-xml dataset is not an option.)

Now, the question is, in code, how do I execute the stored procedure and then write the results to a text file?  If the results of the query were a normal dataset, I could (and have in other cases), called the stored proc, filled a dataset with the results, and then used DataSet.WriteXml(path to text file) to accomplish the same thing.  But in this case, the result of the stored proc ALREADY IS XML, so using WriteXML is not an option.

I am not sure how to go about this same process when the stored proc is already returning an XML string.  Any advice would be great!

Thanks!

Casey

P.S., oh yea, I am doing this with MS SQL 2005 and .NET 2.0 (vb.net, but I can translate from C#) Thanks!