Post
Ask Question

How To Import XML File Data Into SQL Table Using Stored Procedure In ASP.NET

I have an XML file (student), which stores the information of the student and want to import its data into SQL table.

Student.xml file looks, as shown below. 

  1. <?xml version="1.0" encoding="utf-16" ?>  
  2. <students>  
  3.     <student>  
  4.         <name>Tarun</name>  
  5.         <class>X</class>  
  6.         <roll_no>1234</roll_no>  
  7.     </student>  
  8.     <student>  
  9.         <name>Ram</name>  
  10.         <class>X</class>  
  11.         <roll_no>1235</roll_no>  
  12.     </student>  
  13.     <student>  
  14.         <name>Rajeev</name>  
  15.         <class>X</class>  
  16.         <roll_no>1236</roll_no>  
  17.     </student>  
  18.     <student>  
  19.         <name>Gopal</name>  
  20.         <class>X</class>  
  21.         <roll_no>1237</roll_no>  
  22.     </student>  
  23. </students>   

Implementation

I have created a Student table.

Idint
namevarchar(100)
classvarchar(50)
roll_noint

HTML structure of the page looks, as shown below. 

  1. <html xmlns="http://www.w3.org/1999/xhtml">  
  2.   
  3. <head runat="server">  
  4.     <title></title>  
  5. </head>  
  6.   
  7. <body>  
  8.     <form id="form1" runat="server">  
  9.         <div>  
  10.             <fieldset style="width:25%">  
  11.                 <legend></legend>  
  12.                 <table>  
  13.                     <tr>  
  14.                         <td>Upload file :</td>  
  15.                         <td>  
  16.                             <asp:FileUpload ID="FileUpload1" runat="server" />  
  17.                         </td>  
  18.                     </tr>  
  19.                     <tr>  
  20.                         <td></td>  
  21.                         <td>  
  22.                             <asp:Button ID="btnupload" runat="server" Text="Upload" OnClick="btnupload_Click" />  
  23.                         </td>  
  24.                     </tr>  
  25.                 </table>  
  26.             </fieldset>  
  27.         </div>  
  28.     </form>  
  29. </body>  
  30.   
  31. </html>   

Import the namespace

C# code 

  1. using System.Xml;  
  2. using System.Data.SqlClient;  
  3. using System.Configuration;  
  4. using System.IO;  
  5. using System.Data;   

Import XML file

On button click, write the code given below.

C# code 

  1. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());  
  2. protected void btnupload_Click(object sender, EventArgs e) {  
  3.     try {  
  4.         string fileName, filepath, contentXml;  
  5.         fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
  6.         filepath = Server.MapPath("~/upload/") + fileName;  
  7.         FileUpload1.SaveAs(filepath);  
  8.         string xmlfile = File.ReadAllText(filepath);  
  9.         SqlCommand cmd = new SqlCommand("InsertXMLData", con);  
  10.         cmd.CommandType = CommandType.StoredProcedure;  
  11.         cmd.Parameters.AddWithValue("@xmlfile", xmlfile);  
  12.         con.Open();  
  13.         cmd.ExecuteNonQuery();  
  14.         con.Close();  
  15.         Response.Write("<script type=\"text/javascript\">alert('file uploaded successfully');</script>");  
  16.     } catch (Exception ex) {}  
  17. }   

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