Serializing a DataSet Object as XML

You can serialize a populated DataSet object to an XML file by executing the DataSet object's WriteXml method.

In the following sample code we are creating a connection to AdventureWorks database and then populating DataSet with customer data using a DataAdapter and writing the contents of DataSet to an XML file named Customer.xml.

using System.Data;
using System.Data.SqlClient;

namespace DataSetToXML
{
    class Program
    {
        static void Main(string[] args)
        {
            string strFileName = @"..\..\Customer.xml";
 
            //Create Connection
            string strConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string strText = "SELECT Top 5 * FROM Sales.Customer Order By ModifiedDate Desc";

            // Create and fill a DataSet using a data adapter
            SqlDataAdapter objDataAdapter = new SqlDataAdapter(strText, strConnectString);
            DataSet objDataSet = new DataSet("CustomerData");
            // Fill DataSet with customer record
            objDataAdapter.Fill(objDataSet,"Customer");
            // Write DataSet contents to Customer.xml file
            objDataSet.WriteXml(strFileName, XmlWriteMode.IgnoreSchema);
        }
     
    }
}


The resulting XML file contents will look like this:

<?xml version="1.0" standalone="yes"?>
<CustomerData>
  <Customer>
    <CustomerID>6</CustomerID>
    <TerritoryID>4</TerritoryID>
    <AccountNumber>AW00000006</AccountNumber>
    <CustomerType>S</CustomerType>
    <rowguid>1a92df88-bfa2-467d-bd54-fcb9e647fdd7</rowguid>
    <ModifiedDate>2004-10-13T11:15:07.263+05:30</ModifiedDate>
  </Customer>
  <Customer>
    <CustomerID>5</CustomerID>
    <TerritoryID>4</TerritoryID>
    <AccountNumber>AW00000005</AccountNumber>
    <CustomerType>S</CustomerType>
    <rowguid>83905bdc-6f5e-4f71-b162-c98da069f38a</rowguid>
    <ModifiedDate>2004-10-13T11:15:07.263+05:30</ModifiedDate>
  </Customer>
  <Customer>
    <CustomerID>4</CustomerID>
    <TerritoryID>4</TerritoryID>
    <AccountNumber>AW00000004</AccountNumber>
    <CustomerType>S</CustomerType>
    <rowguid>ff862851-1daa-4044-be7c-3e85583c054d</rowguid>
    <ModifiedDate>2004-10-13T11:15:07.263+05:30</ModifiedDate>
  </Customer>
  <Customer>
    <CustomerID>3</CustomerID>
    <TerritoryID>4</TerritoryID>
    <AccountNumber>AW00000003</AccountNumber>
    <CustomerType>S</CustomerType>
    <rowguid>130774b1-db21-4ef3-98c8-c104bcd6ed6d</rowguid>
    <ModifiedDate>2004-10-13T11:15:07.263+05:30</ModifiedDate>
  </Customer>
  <Customer>
    <CustomerID>2</CustomerID>
    <TerritoryID>1</TerritoryID>
    <AccountNumber>AW00000002</AccountNumber>
    <CustomerType>S</CustomerType>
    <rowguid>e552f657-a9af-4a7d-a645-c429d6e02491</rowguid>
    <ModifiedDate>2004-10-13T11:15:07.263+05:30</ModifiedDate>
  </Customer>
</CustomerData>

The XML document is well formed, and it's root node is called CustomerData. You can set the name of the root node by changing the DataSet object's DataSetName property. This property can be changed at any time. The DataRow objects have been represented as repeating elements in the XML. Each column is represented by an element within the data row element. You can change the format of the column data by assigning a new value to the columnMapping property of the DataColumn objects.

//set column mapping
objDataSet.Tables["Customer"].Columns["CustomerID"].ColumnMapping = MappingType.Attribute;
            objDataSet.Tables["Customer"].Columns["TerritoryID"].ColumnMapping = MappingType.Attribute;
            objDataSet.Tables["Customer"].Columns["AccountNumber"].ColumnMapping = MappingType.Attribute;
            objDataSet.Tables["Customer"].Columns["CustomerType"].ColumnMapping = MappingType.Attribute;
            objDataSet.Tables["Customer"].Columns["ModifiedDate"].ColumnMapping = MappingType.Attribute;
            objDataSet.Tables["Customer"].Columns["rowguid"].ColumnMapping = MappingType.Hidden;

In the last line we have used MappingType.Hidden to prevent the "rowguid" column from storing it's data.

Note : ColumnMapping property affects only the generated XML when writing to XML.

The resulting XML file contents will look like this :

<?xml version="1.0" standalone="yes"?>
<CustomerData>
  <Customer CustomerID="6" TerritoryID="4" AccountNumber="AW00000006" CustomerType="S" ModifiedDate="2004-10-13T11:15:07.263+05:30" />
  <Customer CustomerID="5" TerritoryID="4" AccountNumber="AW00000005" CustomerType="S" ModifiedDate="2004-10-13T11:15:07.263+05:30" />
  <Customer CustomerID="4" TerritoryID="4" AccountNumber="AW00000004" CustomerType="S" ModifiedDate="2004-10-13T11:15:07.263+05:30" />
  <Customer CustomerID="3" TerritoryID="4" AccountNumber="AW00000003" CustomerType="S" ModifiedDate="2004-10-13T11:15:07.263+05:30" />
  <Customer CustomerID="2" TerritoryID="1" AccountNumber="AW00000002" CustomerType="S" ModifiedDate="2004-10-13T11:15:07.263+05:30" />
</CustomerData>

MappingType Enumeration Members

1. Attribute : The column data is placed in an XML attribute.

2. Element : The default. The column data is placed in an XML element.

3. Hidden : The column data is not sent to the XML file.

4. SimpleContent : The data is stored as text like the Element setting but without the additional element tag.