[thelist] Stored Proc to XML to Text File?

[Casey Crookston]

----- Original Message ----- 

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.

----- Solution ----- 

In case anyone is interested:

Dim XD As XmlDocument = New XmlDocument()
Dim myCommand As New SqlCommand("Create_Category_File", dbConn)
myCommand.CommandType = CommandType.StoredProcedure
dbConn.Open()
Dim XR As XmlReader = myCommand.ExecuteXmlReader
XD.Load(XR)
dbConn.Close()
XD.Save(HttpContext.Current.Server.MapPath("../XMLFlatFiles/Categories.xml"))

I'd be curious to know, however, if it's possible to do this with a 
dataAdapter so a specific connection does not need to be opened and closed.

Thanks!

Casey