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.

Next : How to create an Excel file from XML

Download Source Code

Print Source Code

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

Related Topics