Reader Level:
Articles

How to Write Data of an SQL Server Table to an XML File Using C#

By Deepak Sharma on December 03, 2012
In this article I will discuss how to write data of an SQL Server table to an XML file using C#.
  • 0
  • 0
  • 17770

Introduction

In this article I will discuss how to write data of an SQL Server table to an XML file using C#.

Description

I have an Employees table in a Northwind database with some sample data that we are going to write to an XML file. You can download Northwind and pubs sample databases from here.

xml.jpg

We will discuss two methods to convert a database table to an XML file. In the first method, we will use the WriteXml method of DataTable to write the XML to a file. In the second method we will use a SELECT statement with a FOR XML clause to select data from the database in XML format and then we use the ExecuteXmlReader method of the SqlCommand class to load XML in an XmlDocument object.

Method 1

private static void TableToXml()

{

    try

    {

        string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

        string CmdString = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath FROM Employees";

 

        SqlConnection con;

        SqlCommand cmd;

        SqlDataAdapter sda;

        DataTable dt;

 

        using (con = new SqlConnection(ConString))

        {

            cmd = new SqlCommand(CmdString, con);

 

            con.Open();

            dt = new DataTable("Employees");

            sda = new SqlDataAdapter(cmd);

            sda.Fill(dt);

            dt.WriteXml("Employees.xml");

            con.Close();

        }

    }

    catch (Exception)

    {

        throw;

    }

}

Method 2

private void TableToXml()

{

    try

    {

        string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

        string CmdString = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath " +

                         "FROM Employees FOR XML RAW('Employee'), ROOT('Employees'), ELEMENTS";

 

        SqlConnection con;

        SqlCommand cmd;

        XmlReader reader;

        XmlDocument xmlDoc;

 

        using (con = new SqlConnection(ConString))

        {

            cmd = new SqlCommand(CmdString, con);

            con.Open();

 

            reader = cmd.ExecuteXmlReader();

            xmlDoc = new XmlDocument();

 

            while (reader.Read())

            {

                xmlDoc.Load(reader);

            }

 

            xmlDoc.Save("Employees.xml");

        }

    }

    catch (Exception)

    {               

        throw;

    }

}

Note

By default the FOR XML clause returns the column's values as attributes. The ELEMENTS parameter is added to the above SELECT statement to return the column's values as elements.
 

Deepak Sharma

Deepak is a Software Developer with over 4 years of experience. His expertise is in Windows Forms, ASP.NET, WPF, SQL Server and MySql.Twitter : @idpkshrBlog : http://deepak-sharma.net

Personal Blog: http://deepak-sharma.net
COMMENT USING

Trending up