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.