Generating very large XML file with Linq-to-XML and Linq-to-SQL

Question

I'm trying to do a dump to XML of a very large database (many gigabytes). I'm using Linq-to-SQL to get the data out of the database and Linq-to-XML to generate XML. I'm using XStreamingElement to keep memory use low. The job still allocates all available memory, however, before keeling over without having written any XML. The structure looks like this:

var foo =
    new XStreamingElement("contracts",
       <LinqtoSQL which fetches data>.Select(d =>
    new XElement("contract",
        ... generate attributes etc...
using (StreamWriter sw = new StreamWriter("contracts.xml"))
{
    using (XmlWriter xw = XmlWriter.Create(sw))
    {
        foo.WriteTo(xw);
    }
}

I've also tried saving with:

foo.Save("contracts.xml", SaveOptions.DisableFormatting);

...to no avail.

Any clues?

Answer 1

Sure, you only need one clue for that: don't do it. :-)

XML is not an adequate format for database dumps because it does not handle large amounts of data well.

All databases have some sort of "dump" utility to export their data in a format that can then be read into another database - that would be the way to go.

Ron

Answer 2

How complex is the data? I'm not overly familiar with XStreamingElement, but I wonder if you might have more joy using XmlWriter directly? Especially for like data in a loop, it can be used pretty easily.

I would, however, have concerns over xml as the choice for this data. Is this s requirement? Or simply a convenient available format? In particular, it can be hard to parse that size of xml conveniently, as you'd have to use XmlReader (which is harder to get right than XmlWriter).

If you can use other formats, I'd advise it... a few leap to mind, but I won't babble on unless you mention that you'd be interested.

Answer 3

Right, "solved" the problem by chunking my data into sets of 10,000 items and writing them to separate XML files. Will ponder other data exchange format and buy a larger server.

I would still be mighty interesting if someone had figures out how to properly take advantage of XStreamingElement.