Insert XML data in SQL Table using C# and VB.Net in ASP.Net

Hi fredo1094,

Refer below sample.

HTML

<asp:Button ID="Button1" Text="Upload XML" runat="server" OnClick="UploadXML" />
<br />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <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;
using System.Data.SqlTypes;
using System.Xml;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Xml

Code

C#

protected void UploadXML(object sender, EventArgs e)
{
    string xml = @"<?xml version='1.0' encoding='utf-8'?>  
                    <Customers>
                        <Customer Id ='1'>
                        <Name>John Hammond</Name>
                        <Country>United States</Country>
                        </Customer>
                        <Customer Id = '2'>
                        <Name>Mudassar Khan</Name>
                        <Country>India</Country>
                        </Customer>
                        <Customer Id ='3'>
                        <Name>Suzanne Mathews</Name>
                        <Country>France</Country>
                        </Customer>
                        <Customer Id ='4'>
                        <Name>Robert Schidner</Name>
                        <Country>Russia</Country>
                        </Customer>
                    </Customers>";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        SqlCommand cmd = new SqlCommand("InsertXML");
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter parameter1 = cmd.Parameters.Add("@xml", SqlDbType.Xml);
        parameter1.Value = new SqlXml(new XmlTextReader(xml, XmlNodeType.Document, null));
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        cmd = new SqlCommand("SELECT CustomerId, Name,Country FROM CustomerDetails", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}

VB.Net

Protected Sub UploadXML(ByVal sender As Object, ByVal e As EventArgs)
    Dim xml As String = "<?xml version='1.0' encoding='utf-8'?>  "
    xml += "<Customers>"
    xml += "<Customer Id ='1'>"
    xml += "<Name>John Hammond</Name>"
    xml += "<Country>United States</Country>"
    xml += "</Customer>"
    xml += "<Customer Id = '2'>"
    xml += "<Name>Mudassar Khan</Name>"
    xml += "<Country>India</Country>"
    xml += "</Customer>"
    xml += "<Customer Id ='3'>"
    xml += "<Name>Suzanne Mathews</Name>"
    xml += "<Country>France</Country>"
    xml += "</Customer>"
    xml += "<Customer Id ='4'>"
    xml += "<Name>Robert Schidner</Name>"
    xml += "<Country>Russia</Country>"
    xml += "</Customer>"
    xml += "</Customers>"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim cmd As SqlCommand = New SqlCommand("InsertXML")
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        Dim parameter1 As SqlParameter = cmd.Parameters.Add("@xml", SqlDbType.Xml)
        parameter1.Value = New SqlXml(New XmlTextReader(xml, XmlNodeType.Document, Nothing))
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        cmd = New SqlCommand("SELECT CustomerId, Name,Country FROM CustomerDetails", con)
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        gvCustomers.DataSource = dt
        gvCustomers.DataBind()
    End Using
End Sub

Screenshot