Read XML from Sproc and Write to File

I need to read a large xml result (using For XML) from a stored proc and wr开发者_开发技巧ite it to a file in a .Net app. My first shot is to read the xml from the proc using XmlReader. I then need to write it to a file. Is this best way to handle this scenario, or is there a "better" method?

If this may be more convenient, you may also write the xml to a file directly from the stored procedure using bcp and cmdshell, as in this example:

DECLARE @exe nvarchar(1000)
SET @exe =N'bcp '
SET @exe =@exe + N'"SELECT id, Color FROM Tinker.dbo.myTable AS myTable FOR XML AUTO" '
SET @exe =@exe + N'queryout E:\DB\Colors.xml -c -T'
EXEC master..xp_cmdshell @exe

For this to work, you need to have xp_cmdshell enabled, also make sure that SQL server service has permission to write to the directory.

You're doing exactly what I would do.

The Reader family of APIs in .Net don't go well with copy semantics, as you discovered. To copy an XML Reader you would have to do it node by node, which is slow and very error prone.

I would say that a better alternative to your problem is using a raw stream, not a reader. Streams have buffer byte[] Read and Write semantics that allow for a simple copy. To obtain a proper stream from ADO.Net you have to use ExecuteReader and you must pass in the CommandBehavior.SequentialAccess so that the data reader does not cache your 200Mb document, and you must get the field of interest as one of the SqlClient types with stream semantics, of which there aren't a many lot: SqlBytes.Stream.