[thelist] Stored Proc to XML to Text File?
Casey Crookston
caseyb at thecrookstons.com
Tue Mar 18 01:01:38 CDT 2008
----- 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
More information about the thelist
mailing list