How to insert data from xml to database
XML is a general purpose tag based language and very easy to transfer and store data across applications. The .Net technology is widely supported XML file format. The .Net Framework provides the Classes for read, write, and other operations in XML formatted files . Moreover the Dataset in ADO.NET uses XML format as its internal storage format.
Here we are going to insert the values of an XML file to a Database Table using SQL insert command. Here the Dataset using an XmlReader for read the content of the XML file - Product.XML . Locate the XML file using XmlReader and pass the XmlReader as argument of Dataset. Also establish a connection to the Database using a connectionstring . After getting the data from XML file to the Dataset , we can loop through the dataset values and use insert command to add the values to the Product table in the Databse.
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
|