How to convert a database table to an XML file

• Download source code - 2.47 Kb

Introduction

XML is definitely the best way to share data via the World Wide Web. I came across a situation where I needed to export the database table rows in XML file format. XML data can easily be integrated into web applications for many uses. However, at some point you want XML data added to a database and vice-versa.

ADO.NET Architecture

ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. The DataSet can be populated with data from an XML source, whether it is a file or an XML stream. The DataSet can be written as World Wide Web Consortium (W3C) compliant XML, including its schema as XML Schema Definition language (XSD) schema, regardless of the source of the data in the DataSet.

The following table outlines the four core objects that make up a .NET data provider:

I did this in ASP.NET with VB.NET code behind using "System.IO.StreamWriter". It is assumed that you have a SQL Server database running with tables in it.

Connection to Database

Start with the database connection here. I used SQL Server 2000 as my database. Write the following code in your web config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="ConnectionString" 
     value="data source=erp1;Initial Catalog=Inventory; UID=sa;pwd="/>
    </appSettings> 
  <system.web>

Add the following line of code in your .aspx page:

Protected connect As SqlConnection = _
    New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
'Declaring the command, SqlDataAdapter and DataSet 

Dim cmdXML As SqlCommand
Dim DScmdXML As SqlDataAdapter
Dim DSXML As New DataSet()

In this example, I have taken a ListBox which contains all the SQL Server database table names, manually adding the database table names as shown:

<asp:listbox id="ListBox1" runat="server" 
    Height="120px" Width="186px" SelectionMode="Multiple">
      <asp:ListItem Value="1">Category</asp:ListItem>
      <asp:ListItem Value="2">SubCategory</asp:ListItem>
      <asp:ListItem Value="3">Product</asp:ListItem>
      <asp:ListItem Value="8">City</asp:ListItem>
      <asp:ListItem Value="9">State</asp:ListItem>
</asp:listbox>

To cut short the code I'm considering only the Product table. To test the program, select the "Product" table from the listbox and click the Convert button. The data of the Product table will be converted to Product.xml file. The program is simple and easy to understand. It's better programming to include the server side logic code in a Try … Catch block.

Main Logic

sqlXML = "Select ProductCode, ProductName from Product "
cmdXML = New SqlCommand(sqlXML, connect)
DScmdXML = New SqlDataAdapter(cmdXML)
DScmdXML.Fill(DSXML, "Product")
Response.Flush()
DSXML.WriteXml("Product.xml", XmlWriteMode.WriteSchema)
Dim xmlSW2 As System.IO.StreamWriter = New System.IO.StreamWriter("Product.xml")
DSXML.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)
xmlSW2.Flush()
xmlSW2.Close()

How it works?

1. Trap the user selected tables from the listbox using a 'For' loop and split array.
2. Select Case statement directs to suitable file creation.
3. Open the database connection and open a DataSet as shown in the example.
4. Actual work is done by passing the file name for the resulting XML as a string to WriteXml, and System.IO.StreamWriter writes to the XML file.
5. Number of .xml files created depends on the number of tables selected from the listbox.

With a little trick you can create the XML file in any folder or subfolder and also the reverse is possible: i.e., a .xml file to a DataSet.

Important: Before converting to a .XML file, check the security settings of the folder where the file is saved.