Click here to Skip to main content
Click here to Skip to main content
Go to top

How to convert a database table to an XML file

, 9 Sep 2005
Rate this:
Please Sign up or sign in to vote.
This article explains how to convert a database table to an XML file.

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:

Object Description
Connection Establishes a connection to a specific data source.
Command Executes a command against a data source. Exposes parameters and can execute within the scope of a transaction from a Connection.
DataReader Reads a forward-only, read-only stream of data from a data source.
DataAdapter Populates a DataSet and resolves updates with the data source.

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Mohd Abdul Moyeed
Web Developer
Saudi Arabia Saudi Arabia
Myself Moyeed Worked as a Programmer in India, Mauritius. Now working as a Web-Developer in Kingdom of Saudi Arabia.

Comments and Discussions

 
Questionabut artical PinmemberimDeveloper11-Nov-12 9:34 
QuestionQuery??????? PinmemberimDeveloper11-Nov-12 8:42 
GeneralERROR.. Pinmemberrockstar28315-Apr-10 22:08 
Generalxml to other databases Pinmemberboseg10-Jan-10 0:21 
GeneralPath Pinmembermonaloca10-Nov-09 17:48 
Generalthe palce where the XML file is saved..??~ PinmemberMember 441395817-Apr-09 15:33 
Questionhow to generate databse table (sql)from xml file in asp.net c# coce Pinmemberchanchalmajumdar19-Jul-08 1:00 
GeneralI cannot do it yet Pinmemberalphokung23-Apr-08 17:46 
QuestionHow to convert database table to .Net Class? PinmemberKoundinya10-Sep-07 19:41 
GeneralThank Pinmemberyogeshbhandare25-Mar-07 23:49 
GeneralNested XML PinmemberSaintos25-Jan-07 11:08 
GeneralExcellent Article Pinmemberzeineddine10-Jan-07 8:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 9 Sep 2005
Article Copyright 2005 by Mohd Abdul Moyeed
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid