Click here to Skip to main content
13,193,056 members (81,107 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 9 Sep 2005

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.


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:

ConnectionEstablishes a connection to a specific data source.
CommandExecutes a command against a data source. Exposes parameters and can execute within the scope of a transaction from a Connection.
DataReaderReads a forward-only, read-only stream of data from a data source.
DataAdapterPopulates 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"?>
    <add key="ConnectionString" 

     value="data source=erp1;Initial Catalog=Inventory; UID=sa;pwd="/>

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>

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")
DSXML.WriteXml("Product.xml", XmlWriteMode.WriteSchema)
Dim xmlSW2 As System.IO.StreamWriter = New System.IO.StreamWriter("Product.xml")
DSXML.WriteXml(xmlSW2, XmlWriteMode.WriteSchema)

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.


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


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.

You may also be interested in...

Comments and Discussions

Questionabut artical Pin
imDeveloper11-Nov-12 9:34
memberimDeveloper11-Nov-12 9:34 
QuestionQuery??????? Pin
imDeveloper11-Nov-12 8:42
memberimDeveloper11-Nov-12 8:42 
GeneralERROR.. Pin
rockstar28315-Apr-10 22:08
memberrockstar28315-Apr-10 22:08 
Generalxml to other databases Pin
boseg10-Jan-10 0:21
memberboseg10-Jan-10 0:21 
GeneralPath Pin
monaloca10-Nov-09 17:48
membermonaloca10-Nov-09 17:48 
Generalthe palce where the XML file is saved..??~ Pin
Member 441395817-Apr-09 15:33
memberMember 441395817-Apr-09 15:33 
Questionhow to generate databse table (sql)from xml file in c# coce Pin
chanchalmajumdar19-Jul-08 1:00
memberchanchalmajumdar19-Jul-08 1:00 
GeneralI cannot do it yet Pin
alphokung23-Apr-08 17:46
memberalphokung23-Apr-08 17:46 
QuestionHow to convert database table to .Net Class? Pin
Koundinya10-Sep-07 19:41
memberKoundinya10-Sep-07 19:41 
GeneralThank Pin
yogeshbhandare25-Mar-07 23:49
memberyogeshbhandare25-Mar-07 23:49 
GeneralNested XML Pin
Saintos25-Jan-07 11:08
memberSaintos25-Jan-07 11:08 
GeneralExcellent Article Pin
zeineddine10-Jan-07 8:53
memberzeineddine10-Jan-07 8:53 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171017.2 | Last Updated 9 Sep 2005
Article Copyright 2005 by Mohd Abdul Moyeed
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid