2

I want to save the complete result of a FOR XML SQL Query to a file.

My SQL Query looks something like this:

SELECT * FROM Customer FOR XML RAW

in my code, I now want to execute this query against an SQL Server and read the complete XML result and save it to disk.

My code looks like this:

using (XmlReader xmlResultReader = command.ExecuteXmlReader()) //command is my SqlCommand
using (MemoryStream resultFile = new MemoryStream())
using (StreamWriter writer = new StreamWriter(resultFile, Encoding.UTF8))
{
    while (xmlResultReader.Read())
    {
        writer.WriteLine(xmlResultReader.ReadOuterXml());
    }


    //write stream to file
}

But when I run this, not the complete result of the query gets saved to the MemoryStream. The result is truncated in the middle of a <row /> element in the resulting XML. So not even the returned XML is valid.

I also tried writing the result with an XmlWriter using this code:

xmlWriter.WriteNode(xmlResultReader, false);

but this showed the same result.

So now my question is: How can I get the complete XML result of the query from the XmlReader returned by ExecuteXmlReader()?

dbc
  • 104,963
  • 20
  • 228
  • 340
dczychon
  • 51
  • 1
  • 5
  • Have you tried [How to create an XML file from a XmlReader?](https://stackoverflow.com/q/3988832/3744182). This looks to be a duplicate, as the `XmlReader` in that question is also returned by `SqlCommand.ExecuteXmlReader()` – dbc Jul 28 '19 at 19:33
  • I have tried to do this with the WriteNode method of the XmlWriter. But as I have written above, this will also truncate my xml result. – dczychon Jul 28 '19 at 19:37
  • In that question they set `CommandTimeout = 60000;`. Could that be the problem? [Format XML Returned from SQL Server](https://stackoverflow.com/q/30604603/3744182) also seems very similar. – dbc Jul 28 '19 at 19:38
  • Unfortunately not. I tried setting it to the same value as in the question but nothing changes. The command completes without any exception and the resulting xml is not the complete result that i get in SSMS with this query. – dczychon Jul 28 '19 at 19:43
  • 1
    Also possibly relevant: https://stackoverflow.com/a/40775242 – dbc Jul 28 '19 at 22:57

0 Answers0