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.

Answers


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".


Need Your Help

Tool or approach for PHP product migration from PHP 5.2 to PHP 5.4

php migration version

Existing product developed in core PHP is built in PHP 5.2 and now we want to migrate the product to PHP 5.4.

Android app data storage design

android sqlite

I'm working on an Android app for homework management. I'm a senior in college, so my experience on larger projects is very limited, but I'd like to design all parts of this app well instead of just

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.