Import XML file data into sql table using store procedure in ASP.NET - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

Import XML file data into sql table using store procedure in ASP.NET

In this article I am going to explain how to import xml file data into sql table using store procedure in asp.net.


Description:
I have an xml file (student) which store the information of student and want to import its data into sql table.

Student.xml file look like this:

<?xml version="1.0" encoding="utf-16" ?>
<students>
  <student>
    <name>Tarun</name>
    <class>X</class>
  <roll_no>1234</roll_no>
  </student>
<student>
    <name>Ram</name>
    <class>X</class>
  <roll_no>1235</roll_no>
  </student>
<student>
    <name>Rajeev</name>
    <class>X</class>
  <roll_no>1236</roll_no>
  </student>
<student>
    <name>Gopal</name>
    <class>X</class>
  <roll_no>1237</roll_no>
  </student>
</students>

Implementation:
I have create a table Students

Id
int
name
varchar(100)
class
varchar(50)
roll_no
int

HTML structure of page:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset style="width:25%">
            <legend></legend>
            <table>
                <tr><td>Upload file :</td><td> <asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>
                <tr><td></td><td><asp:Button ID="btnupload" runat="server" Text="Upload" OnClick="btnupload_Click" /></td></tr>
            </table>
        </fieldset>
    </div>
    </form>
</body>
</html>

Import the namespace

C# code:
using System.Xml;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data;

VB.net code:
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Data

Import XML file
On button click write the below given code:

C# code:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());

    protected void btnupload_Click(object sender, EventArgs e)
    {
        try
        {
            string fileName, filepath, contentXml;
            fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            filepath = Server.MapPath("~/upload/") + fileName;
            FileUpload1.SaveAs(filepath);
            string xmlfile = File.ReadAllText(filepath);
            SqlCommand cmd = new SqlCommand("InsertXMLData", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@xmlfile", xmlfile);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            Response.Write("<script type=\"text/javascript\">alert('file uploaded successfully');</script>");
        }
        catch (Exception ex) { }
    }

VB.net code:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ToString())
    Protected Sub btnupload_Click(sender As Object, e As EventArgs) Handles btnupload.Click
        Try
            Dim fileName As String, filepath As String, contentXml As String
            fileName = Path.GetFileName(FileUpload1.PostedFile.FileName)
            filepath = Server.MapPath("~/upload/") & fileName
            FileUpload1.SaveAs(filepath)
            Dim xmlfile As String = File.ReadAllText(filepath)
            Dim cmd As New SqlCommand("InsertXMLData", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@xmlfile", xmlfile)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            Response.Write("<script type=""text/javascript"">alert('file uploaded successfully');</script>")
        Catch ex As Exception
        End Try
    End Sub

Result :
Import XML file data into sql table using store procedure in ASP.NET

Build the project and run. To test upload the XML file.  

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon