Reader Level:
ARTICLE

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

Posted by Deepak Sharma Articles | XML 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
  • 15207

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.
 

COMMENT USING

Trending up