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
  • 91,441
  • 18
  • 186
  • 284
dczychon
  • 51
  • 3
  • 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

1 Answers1

0

I think, DataSet is bet suited for such requirement.Save file to disk is time taking ,you cannot keep connection for so long using SqlDataReader or XMLdataReader.

  1. Load Result set in Dataset.
  2. Loop throgh dataset
  3. Perform validation whether xml file is genuine.

  4. Save file to disk one by one.

    XmlDocument xdoc = new XmlDocument(); xdoc.LoadXml(yourXMLString); xdoc.Save("C:\myfilename.xml");

KumarHarsh
  • 4,894
  • 1
  • 16
  • 20