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();
sdaMyXML.Fill(dtMyXML);
/// 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.WriteStartDocument();
writer.WriteComment("This file is generated by the C#.NET 4.0");
writer.WriteStartElement("Awmds");
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();
sdaCNT.Fill(dtCNT);
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
writer.WriteEndDocument();
writer.Flush();
writer.Close();
}
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()
{
InitializeComponent();
}
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";
try
{
con.Open();
adapter = new SqlDataAdapter(sql, con);
adapter.Fill(dset);
con.Close();
dset.WriteXml("Customer.xml");
MessageBox.Show("Recorded");
}
catch (Exception st)
{
MessageBox.Show (st.ToString());
}
}
}
}