Adding XML Attributes to a DataSet

Question

I want to add an attribute to the dataset declared below whose value is the value of one of the field in the row.
So I want to add the id as shown below.

<root>
    <Table id="GAS-405">
        <apple>2009FA</apple>
        <orange>3.00</orange>
        <pear>BGPR</pear>
        <banana>GAS-405</banana>
    </Table>
</root>

This will help me identify the node later in my application.
Is this possible? Is this easier to do using XMLDocument?

    Dim sdaFoo As SqlDataAdapter = New SqlDataAdapter("SELECT BLAH FROM BLAHBLAH", conn)
    Dim dsFoo As DataSet = New DataSet()
    dsFoo.DataSetName = "apple"
    sdaFoo.Fill(dsFoo)
    dsFoo.WriteXml("C:\Inetpub\wwwroot\foo.xml")

Answer 1

Dataset.WriteXml() is really a convenience method rather than a flexible way of dealing with XML.

You'll need to take another approach. There are a few options:

1. If you're just adding a single attribute, you could hack it into the resulting xml by re-opening the file as an XDocument, adding the attribute to the necessary element, and saving it again. Not too elegant, but easy, and sometimes easy is best. Even better, just use WriteXml() to put your xml into a string, then load the string as your XDocument.

2. Generate the XML from your query directly, rather than as a dataset. Sql Server 2005 and 2008 have some good XML methods that allow you to select a set of rows as XML (SELECT ... FOR XML) and specify what it looks like.

3. Use XmlSerialization for your dataset and inject the attribute using custom control of the serialization process.... which will be way more trouble than it's worth.

4. Store the attribute somewhere else outside of your XML and use some kind of object to keep track of it. Not really sure what your code is like, but that might be a great option.