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">
  3. <head runat="server">
  4. <title></title>
  5. </head>
  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>
  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.