How to insert data from xml to database

By: Rajesh P.S.

In the .NET Framework, there are classes available to perform various operations on XML formatted files, including reading, writing, and other manipulations. Additionally, ADO.NET's Dataset uses XML as its internal storage format.

XML to database

In the following scenario, we will illustrate how to insert the values from an XML file into a database table using SQL insert commands. To accomplish this, we will utilize the Dataset class, which will employ an XmlReader to read the contents of the XML file named "Product.XML". The XmlReader will be passed as an argument to the Dataset, allowing it to populate the dataset with the XML data.

To interact with the database, we will establish a connection using a connection string. This connection will provide the necessary connectivity to the database where we intend to insert the XML values. Once the dataset contains the data from the XML file, we can iterate through the dataset values using a loop. Within the loop, we will utilize SQL insert commands to add the values from the dataset to the corresponding table in the database.

product_ID = Convert.ToInt32(ds.Tables(0).Rows(i).Item(0)) Product_Name = ds.Tables(0).Rows(i).Item(1) product_Price = Convert.ToDouble(ds.Tables(0).Rows(i).Item(2)) sql = "insert into Product values(" & product_ID & ",'" & Product_Name & "'," & product_Price & ")" command = New SqlCommand(sql, connection)

By executing the insert commands within the loop, we can insert each value from the XML file into the respective table in the database. This process ensures that the data from the XML file is properly mapped and stored in the appropriate database table.

Full Source VB.NET

Imports System.Xml Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim connection As SqlConnection Dim command As SqlCommand Dim adpter As New SqlDataAdapter Dim ds As New DataSet Dim xmlFile As XmlReader Dim sql As String Dim product_ID As Integer Dim Product_Name As String Dim product_Price As Double 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) Dim i As Integer connection.Open() For i = 0 To ds.Tables(0).Rows.Count - 1 product_ID = Convert.ToInt32(ds.Tables(0).Rows(i).Item(0)) Product_Name = ds.Tables(0).Rows(i).Item(1) product_Price = Convert.ToDouble(ds.Tables(0).Rows(i).Item(2)) sql = "insert into Product values(" & product_ID & ",'" & Product_Name & "'," & product_Price & ")" command = New SqlCommand(sql, connection) adpter.InsertCommand = command adpter.InsertCommand.ExecuteNonQuery() Next connection.Close() End Sub End Class

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

Click here to download the input file product.xml

Conclusion

The provided solution demonstrates how to insert values from an XML file into a database table using SQL insert commands. By using the capabilities of the Dataset class, XmlReader, and SQL commands, we can effectively transfer the data from the XML file to the database table, enabling efficient data storage and retrieval operations within the application.

Next > How to create an Excel file from XML

Related Topics

More Related Topics.....