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

convert dataset to xml with schema c#
c# dataset to xml with attributes
how to create datatable from xml file in c#
how to write xml file in c#
how to read and write data from xml file in c#
read and write xml file in asp net
dataset in c# example
c# dataset xsd example

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

Writing DataSet Contents as XML Data, NET you can write an XML representation of a DataSet, with or without its schema. The schema contains the table definitions of the DataSet as well as the The XML representation of the DataSet can be written to a file, a stream, C# Copy. string xmlDS = custDS.GetXml();. GetXml returns the XML  The WriteXml method provides a way to write either data only, or both data and schema from a DataSet into an XML document, whereas the WriteXmlSchema method writes only the schema. To write both data and schema, use one of the overloads that includes the mode parameter, and set its value to WriteSchema.

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.

DataSet.WriteXml Method (System.Data), How to write a DataSet to an XML file using C#. The WriteXml method writes the current data (the schema and data) of a DataSet object to an  How to create XML file from Dataset XML is a tag based language, that means the document is made up of XML tags that contain information. We can create an XML file in several ways. In the previous section we created an XML file using XmlTextWriter Class. Here we are creating an XML file Product.XML using an ADO.NET Dataset.

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.

Writing XML Using DataSet in C#, Framework. • To create custom classes that inherit from the XmlReader and XmlWriter classes public static DataSet GetDataSet(string sql,SqlParameter[] p)​. GetXml returns the XML representation of the DataSet without schema information. To write the schema information from the DataSet (as XML Schema) to a string, use GetXmlSchema. To write a DataSet to a file, stream, or XmlWriter, use the WriteXml method. The first parameter you pass to WriteXml is the destination of the XML output.

Creating Custom XmlReader and XmlWriter Classes, NET's DataSets and how you can use them with XML files. The sample provided is a simple application written in C#. If you use a SQL query every time you need to change something, processing each record may It is useful when you want to write an XML file that has schema information embedded. Working with XML files. DataSets can work with XML files very easily. There are two methods to serialize a DataSet object. These are DataSet::WriteXml and DataSet::WriteXmlSchema. The first one writes data to the XML file and may include also schema information. It is useful when you want to write an XML file that has schema information embedded.

Introduction to DataSets and working with XML files, Here we are creating an XML file Product.XML using an ADO.NET Dataset. In order to create this, we have to manually create a Datatable first and add the data of  We can validate XML using XSD schema file. Please see below steps and code. Let’s create C# solution to validate XML data, Create console application. Steps – File-New - Project and give appropriate name for example – ValidateXML; Let’s Add XML. Steps – Right click on Solution file - Add New Item - XML File. XML File looks as below,

How to create XML file from Dataset - CSharp, You need to save a DataSet as an XML file and create a DataSet from an XML file. The XML schema and data for the DataSet is written both to a file and to a text box on the form. The C# code is shown in Example 8-3. to Connect to SQL Server · Connecting to a Named Instance of SQL Server or Microsoft Data Engine  Loading a DataSet from XML. 03/30/2017; 5 minutes to read +6; In this article. The contents of an ADO.NET DataSet can be created from an XML stream or document. In addition, with the .NET Framework you have great flexibility over what information is loaded from XML, and how the schema or relational structure of the DataSet is created.

Comments
  • Thanks for the reply. I'm not 100% sure how that would work. I updated the original question to include some more info. How does XDocument know where to put the data in between the tags?
  • @user526549 - I think @decyclone is suggesting that you use the XDocument methods to manually manipulate the XML generated by WriteXML into the format that you want. Looks like WriteXML gets you most of the way there - all you need is to replace the two outer tags with your Customer tag.
  • Thanks for the help. Have you guys got any example links at all? I can't seem to get this to work. I've tried adding doc.Add(new XElement("customers")); but that doesn't work. I tried a few other different combinations but I'm not getting anything. By the way I had to add ms.Position = 0; in between the ds.WriteXML and the XDocument doc for it to work for me. Other than that I'd get a root element is missing error.
  • Ok I've given the dataset a name to add customers as the root element. I now have <Table> which needs to be removed. I want to the records to display without the <Table></Table> at the start and end of each record. I tried this XElement table = doc.Element("Table"); table.Remove(); but that errors out. doc.save is underneath that. Any help would be appreciated.