0

I need to read a large xml result (using For XML) from a stored proc and write 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?

Bob
  • 115
  • 7

3 Answers3

1

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.

Damir Sudarevic
  • 21,112
  • 2
  • 42
  • 67
0

You're doing exactly what I would do.

Gabriel McAdams
  • 51,587
  • 10
  • 58
  • 76
  • OK, if you don't mind ;) ....how do you write to a file once you have the XmlReader? Mind you I'm talking a good 200mb file when all is said and done. – Bob Dec 23 '09 at 17:04
  • Take a look at this question on stackoverflow: http://stackoverflow.com/questions/955911/how-to-write-super-fast-file-streaming-code-in-c – Gabriel McAdams Dec 23 '09 at 18:07
  • and this, too: http://stackoverflow.com/questions/1862982/c-filestream-optimal-buffer-size-for-writing-large-files – Gabriel McAdams Dec 23 '09 at 18:08
  • Thanks. But, I meant specifically how do I get one line at a time with XmlReader and then write that. It's my confusion around with method of XmlReader to use. – Bob Dec 23 '09 at 18:12
0

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.

Remus Rusanu
  • 273,340
  • 38
  • 408
  • 539