C# .NET Write XML File from SQL Dataset with Custom Schema

I have the following code in C# Visual Studio 2010.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Database\\db.mde";
string sql = "SELECT * FROM Customer";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);

DataSet ds = new DataSet();
connection.Open();
adapter.Fill(ds, "Test Table");
connection.Close();
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Test Table";
ds.WriteXml("C:\\Users\\Desktop\\testfile.XML");

Now it does everything I want but I need to slightly modify the format of the XML file 开发者_开发知识库when it exports. Can this be easily done? I gather I need to provide a schema file which is fine but I'm not sure how to implement it with the dataset.

Currently the XML looks like this.

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Test_x0020_Table>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
  </Test_x0020_Table>
</NewDataSet>

... but I want it to look like this

<?xml version="1.0" standalone="yes"?>
<Customers>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
</Customers>

Any help would be greatly appreciated.

---

The first node is the name of your table.

You can change this by setting a name on your DataSet.

DataSet ds = new DataSet("Customers"); //plural

---

The inner node is the record.

You can change this by specifying the name when you fill the adapter.

adapter.Fill(ds, "Customer"); //singular

---

This will give you a result like:

 
<Customers>
  <Customer>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
  </Customer>
</Customers> 

This means if you return multiple results you would end up with:

 
<Customers>
  <Customer>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
  </Customer>
  <Customer>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
  </Customer>
  <Customer>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
  </Customer>
</Customers> 

You can't have a single "Customer" because when you write the XML it's formatting it as "Table" with "Records".

---

Assuming that your XML will always be of a structure similar to

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Test_x0020_Table>
    <name>Customer1</name>
    <address>25 Big St</address>
    <suburb>Sydney NSW</suburb>
    <contact>Fred Nurk</contact>
    <phone>11 1111 1111</phone>
  </Test_x0020_Table>
</NewDataSet>

You can do something like this:

string XMLPath = "C:\\Users\\Desktop\\testfile.XML";

XDocument XMLPreModification = new XDocument.Load(XMLPath);
XDocument XMLModified = new XDocument;

XMLDoified.Add(New XElement("Customers", XMLPreModification.Descendants.Descendants()));

XMLModified.Save(XMLPath);

This is just making a new XDocument adding a Customer Element and inserting the Nodes you want into that element, then saving it out to the file.

---

How about something like this :

        using (MemoryStream ms = new MemoryStream())
        {
            ds.WriteXml(ms);
            XDocument doc = XDocument.Load(ms);

            // change XML using doc

            doc.Save("C:\\Users\\Desktop\\testfile.XML");                
        }

--edit--

Here is a little test that I created on how you can transform your xml using LINQ to XML:

    private void XmlTest()
    {
        String xml = "<NewDataSet><Test_x0020_Table><name>Customer1</name><address>25 Big St</address><suburb>Sydney NSW</suburb><contact>Fred Nurk</contact><phone>11 1111 1111</phone></Test_x0020_Table></NewDataSet>";

        XDocument doc = XDocument.Parse(xml);
        XElement element = doc.Descendants("Test_x0020_Table").First();
        XElement newElement = new XElement("Customers", element.Descendants());

        MessageBox.Show(newElement.ToString());
    }

Use above code to manipulate your xml and save it using doc.Save() method.