How to create XML file using data from SQL Database

In this article, a very simple and basic XML file has been created taking data from table of a SQL database in C# code. After creating XML file it has been saved to local disk. Here I've used some built-in function such as WriteStartElement(), WriteElementString() to generate XML file.

XML file is popular for various type of usage. In order to transfer data from one system to another system XML file is suitable. Here I'm going to create a function 'XMLWriter' in which an XML file will be created to take data from Database and will be save with auto file name to a specific folder. In this code snippet the following C# built-in functions are used.

1. WriteStartDocument()
2. WriteComment()
3. WriteStartElement()
4. WriteStartElement()
5. WriteElementString()
6. WriteEndElement()

private void GenerateXML(string VSL, string Voyage)
// Database connection
SqlConnection cnMyXML = new SqlConnection("Server=LPT02DHK\\SQL;Database=seaborne;Connect Timeout=10;User ID=sa;Password=123;");
string qryMyXML = "SELECT * FROM BLInfo WHERE Vessel='"+VSL+"' && Voyage='"+Voyage+"'";
SqlDataAdapter sdaMyXML = new SqlDataAdapter(qryMyXML, cnMyXML);
DataTable dtMyXML = new DataTable();
/// XML file name and path
string FileName, FN, strTime, strDate;
strDate = DateTime.Now.ToString().Remove(10, 12);
strDate = strDate.Remove(2, 1);
strDate = strDate.Remove(4, 3);
strTime = string.Format("{0:HH:mm:ss}", DateTime.Now);
strTime = strTime.Remove(2, 1);
strTime = strTime.Remove(4, 1);
FN = "MyCompany"+ "_" + strDate + strTime;
FileName = "F:" + "\\" + FN + ".xml";
XmlWriter writer = XmlWriter.Create(FileName);
//Start of writing XML file
writer.WriteComment("This file is generated by the C#.NET 4.0");
writer.WriteStartElement("General_segment"); // Start General Segment
writer.WriteElementString("MyCompany_COde", "301");
writer.WriteElementString("Vessel", VSL);
writer.WriteElementString("Voyage_number", Voyage);
writer.WriteElementString("Date_of_Creation", DateTime.Now);
writer.WriteEndElement();//End General Segment
for (int i = 0; i < dtMyXML.Rows.Count; ++i)
writer.WriteStartElement("Bol_segment");//Start Bol segment
writer.WriteElementString("Bol_No", dtMyXML.Rows[i]["BLNo"]);
writer.WriteElementString("Line_number", dtMyXML.Rows[i]["LineNo"]);
writer.WriteStartElement("Shipper"); // Start Shipper segment
writer.WriteElementString("Shipper_Name", dtMyXML.Rows[i]["Shipper"]);
writer.WriteElementString("Address", dtMyXML.Rows[i]["Address"]);
writer.WriteElementString("Identity", dtMyXML.Rows[i]["Identity"]);
writer.WriteEndElement();//End Shipper
// Open container table
string qryCNT = "SELECT * FROM Container WHERE BLNo='"+dtMyXML.Rows[i]["BLNo"]+"'";
SqlDataAdapter sdaCNT = new SqlDataAdapter(qryCNT, cnMyXML);
DataTable dtCNT = new DataTable();
for (int j = 0; j < dtCNT.Rows.Count; ++j)
writer.WriteStartElement("ctn_segment"); // Start of Container tag
writer.WriteElementString("Container_No", dtCNT.Rows[j]["CNTNo"]);
writer.WriteElementString("Type_of_container", dtCNT.Rows[j]["CNTType"]);
writer.WriteElementString("Seal_number", dtCNT.Rows[j]["SealNo"]);
writer.WriteEndElement();//End container
writer.WriteEndElement();//End Bol segment
writer.WriteEndElement();// End Awmds


Author: Phagu Mahato10 Sep 2013 Member Level: Gold   Points : 7

Thank you Abul for this useful article on create XML file using data from SQL Database. I also post some code snippet in C# like below;
using System;
using System.Data;
using System.Windows.Forms;
using System.Xml;
using System.Data.SqlClient;

namespace MyWindowsApplication
public partial class Form2 : Form
public Form2()

private void btn1_Click(object sender, EventArgs e)
string connetionString = null;
SqlConnection connection );
SqlDataAdapter adapter ;
DataSet dset = new DataSet();
string sql = null;

connetionString = "Data Source=servername;Initial Catalog=databsename;User ID=Admin;Password=psword1234";
con = new SqlConnection(connetionString);
sql = "select * from Customer";
adapter = new SqlDataAdapter(sql, con);
catch (Exception st)
MessageBox.Show (st.ToString());


Guest Author: Dash18 Sep 2013

What an awesome way to explain this-now I know everything!

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: