How to: Generate XML file from database table

In this article, I am going to show how we can generate a XML file from our Sql Server database. I am going to explain this task with a simple example.

The default.aspx.cs code is:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.IO;

public partial class _Default : System.Web.UI.Page

{   

    SqlConnection con;

    SqlCommand cmd = new SqlCommand();

    SqlDataAdapter da = new SqlDataAdapter();

    DataSet ds = new DataSet();

    protected void Page_Load(object sender, EventArgs e)

    {

        string XMLFileName = "D:/XmlFileFromDataBaseData";

        string connString = "Data Source=Local; Initial Catalog=northwind; Uid=sa; pwd=";

        string Query = "select * from Employees";

        XMLGenarate(connString, Query, XMLFileName);

        Response.Write(XMLFileName);

    }

    public void XMLGenarate(string connstring, string Query, string FileName)

    {

        string XMLFileName = FileName;

        con = new SqlConnection(connstring);

        da.SelectCommand = new SqlCommand(Query, con);

        da.Fill(ds);

        FileStream fs = null;

        fs = new FileStream(XMLFileName + ".xml", FileMode.OpenOrCreate, FileAccess.Write);

        ds.WriteXml(fs);

        fs.Close();

    }  

}

Here in this application, I am using Employees table of northwind DataBase. The location for generated XML file is  "D:/". When user run the application then we can see the XML file has been generated. The output of the XML file will look something like this:



Figure 1: A XML File of a table.