How to write top-level XML element values after writing sub elements

Question

I'm looking for advice here on a smart solution to my problem.

I'm writing an XML Document using the XMLWriter class and reading data out of an ADO.NET DataReader in a forward-only fashion. In the top of my XML file I need to have elements like so:

<datefrom>2010-07-08</datefrom>
<dateto>2010-07-10</dateto>
<total>335.00</total>

<datefrom> needs to be the earliest date found in the data. <dateto> is the latest date. <total> is the sum of all <totalpaid>115.00</totalpaid> elements listed later in the document.

As I create/write the XML elements to the file I can keep track of the earlier/latest dates and I can keep summing together all the <totalpaid> amounts to get a total.

But...

Once I've reached the end of my DataReader, how would you recommend getting those values into the elements at the top of the XML file? Should I put replacement tokens as the values between the opening and closing tags (i.e. @datefrom, @dateto, @total) and replace them somehow? Is that possible? Should I write & close the file, re-open it and replace the tokens with the actual values? Is there some way to replace the tokens with the values before writing the XML to the file? I haven't worked much with generating XML files so I don't know if there's some standard way to go about doing this.

Thanks!

Answer 1

A XmlWriter is forward-only so you cannot insert elements in the beginning of the document. One possibility is to perform two SQL queries to your database. The first one will aggregate data and return you the three values which you write in the beginning, then you perform your second query to write the rest. This way the aggregation (min, max, total) is done at the database level and you don't need to calculate them in your code.

If you can't do this you could use a XDocument which will allow you inserting nodes anywhere but it has the whole document in memory which may not be acceptable if the document is big.