How to insert data from XML to database

XML is a versatile and widely adopted tag-based language that offers seamless data transfer and storage capabilities across various applications. The .NET technology highly supports XML as a file format, recognizing its significance and wide acceptance. Within the .NET Framework, developers can use a comprehensive set of classes specifically designed for reading, writing, and performing various operations on XML-formatted files. Furthermore, the Dataset component in ADO.NET utilizes XML format as its internal storage mechanism, further emphasizing the importance of XML in facilitating efficient data management and storage within the ADO.NET framework.

XML to database

In this scenario, our objective is to insert the values from an XML file into a database table using SQL's Insert Command. To accomplish this, we utilize a Dataset, which employs an XmlReader to read the contents of the XML file, specifically the "Product.XML" file. By locating the XML file using the XmlReader and passing it as an argument to the Dataset, we establish a connection to the database using a connection string. After retrieving the data from the XML file into the Dataset, we can iterate through the Dataset values and employ the Insert Command to add the values to the "Product" table within the database. This process enables the seamless transfer of data from the XML file to the designated table, facilitating efficient data management within the database system.

Full Source C#
using System; using System.Data; using System.Windows.Forms; using System.Xml; using System.Data.SqlClient; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection connection; SqlCommand command ; SqlDataAdapter adpter = new SqlDataAdapter(); DataSet ds = new DataSet(); XmlReader xmlFile ; string sql = null; int product_ID = 0; string Product_Name = null; double product_Price = 0; connetionString = "Data Source=servername;Initial Catalog=databsename;User ID=username;Password=password"; connection = new SqlConnection(connetionString); xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings()); ds.ReadXml(xmlFile); int i = 0; connection.Open(); for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { product_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]); Product_Name = ds.Tables[0].Rows[i].ItemArray[1].ToString(); product_Price = Convert.ToDouble(ds.Tables[0].Rows[i].ItemArray[2]); sql = "insert into Product values(" + product_ID + ",'" + Product_Name + "'," + product_Price + ")"; command = new SqlCommand(sql, connection); adpter.InsertCommand = command; adpter.InsertCommand.ExecuteNonQuery(); } connection.Close(); MessageBox.Show("Done .. "); } } }

You have to pass necessary database connection information to connection string.

Click here to download the input file product.xml : product.xml






Related Topics
  1. How to XML in C#
  2. How to create an XML file in C#
  3. How to open and read XML file in C# , XmlReader , XmlTextReader , XmlDocument
  4. How to create XML file from Dataset
  1. How to Open and read an XML file to Dataset
  2. How to create an XML file from SQL
  3. How to search in a XML file
  4. How to filter in a XML file
  5. How to create Excel file from XML
  6. How to create XML file from Excel
  7. How to XML to DataGridView
  8. How to create a TreeView from XML File
  9. How to create a Crystal Reports from XML File
  10. XML Serialization Tutorial
  11. XML Serialization
  12. XML DeSerialization