Read XML and bulk insert into Database table using C# and VB.Net

Hi lejogeorge,

Please refer below sample.

Database

CREATE TABLE Students
(
	[Id] Int Primary Key IDENTITY,
	[Name] Varchar(50),
)
GO
CREATE TABLE Student_Details
(
	[Id] int FOREIGN KEY REFERENCES Students(Id),
	[Country] Varchar(50)
)
GO
CREATE PROCEDURE [InsertStudentDetails]
      @Name varchar(50),
      @Country varchar(50)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @Id INT
      INSERT INTO Students ([Name]) VALUES (@Name)
      SELECT @Id = SCOPE_IDENTITY()
      INSERT INTO Student_Details ([Id], [Country]) VALUES (@Id, @Country)
END

XML

<?xml version="1.0" encoding="utf-8" ?>
<Students>
  <Student>
    <Id>1</Id>
    <Name>John Hammond</Name>
    <Country>United States</Country>
  </Student>
  <Student>
    <Id>2</Id>
    <Name>Mudassar Khan</Name>
    <Country>India</Country>
  </Student>
  <Student>
    <Id>3</Id>
    <Name>Suzanne Mathews</Name>
    <Country>France</Country>
  </Student>
  <Student>
    <Id>4</Id>
    <Name>Robert Schidner</Name>
    <Country>Russia</Country>
  </Student>
</Students>

HTML

<asp:GridView ID="gvStudents" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("~/Students.xml"));
    gvStudents.DataSource = ds;
    gvStudents.DataBind();
    foreach (DataRow row in ds.Tables[0].Rows)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("InsertStudentDetails", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", row["Name"]);
                cmd.Parameters.AddWithValue("@Country", row["Country"]);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim ds As DataSet = New DataSet()
    ds.ReadXml(Server.MapPath("~/Students.xml"))
    gvStudents.DataSource = ds
    gvStudents.DataBind()

    For Each row As DataRow In ds.Tables(0).Rows
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using cmd As SqlCommand = New SqlCommand("InsertStudentDetails", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@Name", row("Name"))
                cmd.Parameters.AddWithValue("@Country", row("Country"))
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Next
End Sub

Screenshot