Post
Ask Question

How To Convert DataTable To XML In C#

In this article, I'll discuss how to read data from a SQL Server database into a DataTable and then save the DataTable data into an XML file using C#.
 
I have an Employees table in a Northwind database with some sample data that we are going read into a DataTable and 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.
 
Method 1. Use DataTable.WriteXml 
 
In the first method, we will use the WriteXml method of DataTable to write the XML to a file. 
 
The following code reads a database table by executing a command. The data returned into a DataTable. DataTable.WriteXml method takes an XML file name and writes data to it. 
  1. Xml()  
  2. {  
  3. try  
  4. {  
  5. string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;  
  6. string CmdString = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath FROM Employees";  
  7. SqlConnection con;  
  8. SqlCommand cmd;  
  9. SqlDataAdapter sda;  
  10. DataTable dt;  
  11. using (con = new SqlConnection(ConString))  
  12. {  
  13. cmd = new SqlCommand(CmdString, con);  
  14. con.Open();  
  15. dt = new DataTable("Employees");  
  16. sda = new SqlDataAdapter(cmd);  
  17. sda.Fill(dt);  
  18. dt.WriteXml("Employees.xml");  
  19. con.Close();  
  20. }  
  21. }  
  22. catch (Exception)  
  23. {  
  24. throw;  
  25. }  
  26. }  
Method 2. XmlDocument.Save Method
 
In the second method, wel 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. The XmlDocument.Save method saves data into XML.
  1. private void TableToXml()  
  2. {  
  3. try  
  4. {  
  5. string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;  
  6. string CmdString = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath " +  
  7. "FROM Employees FOR XML RAW('Employee'), ROOT('Employees'), ELEMENTS";  
  8. SqlConnection con;  
  9. SqlCommand cmd;  
  10. XmlReader reader;  
  11. XmlDocument xmlDoc;  
  12. using (con = new SqlConnection(ConString))  
  13. {  
  14. cmd = new SqlCommand(CmdString, con);  
  15. con.Open();  
  16. reader = cmd.ExecuteXmlReader();  
  17. xmlDoc = new XmlDocument();  
  18. while (reader.Read())  
  19. {  
  20. xmlDoc.Load(reader);  
  21. }  
  22. xmlDoc.Save("Employees.xml");  
  23. }  
  24. }  
  25. catch (Exception)  
  26. {  
  27. throw;  
  28. }  
  29. }  
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.
 
Summary
In this article, we learned how to read a SQL Server database table into a DataTable or DataSet and export data into an XML file and save it to a file.
 
Further Readings
 
Here are recommended articles:
 
 


Similar Articles