How To Import XML Data In SQL Server Using MVC 5

In this article. I will demonstrate how to import XML data into SQL server using MVC 5 and entity framework. I will create a XML file and upload it into FileUpload in project. I will also use jQuery datatable plugging for searching, shorting and paging.

Introduction

In this article, I will demonstrate how to import XML data into SQL server using MVC 5 and entity framework. I will create a XML file and upload it into FileUpload in project. I will also use jQuery datatable plugging for searching, shorting and paging.

Step 1 

Open SQL server 2014 and create a database table.

  1. CREATE TABLE [dbo].[Product](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Price] [decimal](18, 0) NULL,  
  5.     [Quantity] [int] NULL,  
  6.  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [Id] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  10. ) ON [PRIMARY]  
  11.   
  12. GO  

Step 2

Open Visual Studio 2015, click on New Project, and create an empty web application project.

Screenshot for creating new project 1

SQL Server

After clicking on New Project, one window will appear. Select Web from the left panel, choose ASP.NET Web Application, give a meaningful name to your project, and then click on OK as shown in below screenshot.

Screenshot for creating new project 2

SQL Server 

After clicking on OK one more window will appear; choose empty, check on MVC checkbox and click on OK as shown below screenshot.

Screenshot for creating new project 3

SQL Server 

After clicking on OK, the project will be created with the name of MvcImportXMLData_Demo.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item, then click on it.

Screenshot for adding entity framework 1

SQL Server 

After clicking on new item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory you can give any name) and click on Add.

Screenshot for adding entity framework 2

SQL Server 

After you click on "Add a window", the wizard will open, choose EF Designer from database and click Next.

Screenshot for adding entity framework 3

 SQL Server
After clicking on Next a window will appear. Choose New Connection. Another window will appear, add your server name if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 4

 SQL Server

Connection will be added. Save connect as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding entity framework 5

 SQL Server

After clicking on NEXT another window will appear choose database table name as shown in the below screenshot then click on Finish.

Screenshot for adding entity framework 6

SQL Server 

Screenshot for adding entity framework-7

 SQL Server
 
Entity framework will be added and respective class gets generated under Models folder.

Screenshot for adding entity framework 8

SQL Server 

Following class will be added,

  1. namespace MvcImportXMLData_Demo.Models  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public partial class Product  
  7.     {  
  8.         public int Id { get; set; }  
  9.         public string Name { get; set; }  
  10.         public Nullable<decimal> Price { get; set; }  
  11.         public Nullable<int> Quantity { get; set; }  
  12.     }  
  13. }  

Step 4

Create a class in Models Folder name it ProductMetaData.cs

Screenshot-1

SQL Server

 

Screenshot-2

SQL Server 

Write the following code

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Xml.Serialization;  
  7. namespace MvcImportXMLData_Demo.Models  
  8. {  
  9.     [Serializable]  
  10.     [XmlRoot("product")]  
  11.     public class ProductMetaData  
  12.     {  
  13.         [XmlElement("id")]  
  14.         public int Id { get; set; }  
  15.   
  16.         [XmlElement("name")]  
  17.         public string Name { get; set; }  
  18.   
  19.         [XmlElement("price")]  
  20.         public Nullable<decimal> Price { get; set; }  
  21.   
  22.         [XmlElement("quantity")]  
  23.         public Nullable<int> Quantity { get; set; }  
  24.     }  
  25.   
  26.     [MetadataType(typeof(ProductMetaData))]  
  27.     public partial class Product  
  28.     {  
  29.     }  
  30.     }  

Step 5

Right click on Controllers folder, select Add, then choose Controller as shown in the below screenshot.

SQL Server 

After clicking on controller a window will appear choose MVC5 Controller-Empty click on Add.

SQL Server 

After clicking on Add another window will appear with DefaultController. Change the name to HomeController then click on Add. HomeController will be added under Controllers folder. Remember don’t change the Controller suffix for all controllers, change only highlight, and instead of Default just change Home as shown in the below screenshot.

 SQL Server

Complete code for controller

  1. using MvcImportXMLData_Demo.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.Mvc;  
  7. using System.Xml.Linq;  
  8.   
  9. namespace MvcImportXMLData_Demo.Controllers  
  10. {  
  11.     public class HomeController : Controller  
  12.     {  
  13.         // GET: Home  
  14.         public ActionResult Index()  
  15.         {  
  16.             return View();  
  17.         }  
  18.         public ActionResult GetData()  
  19.         {  
  20.             using (DBModel db = new DBModel())  
  21.             {  
  22.                 List<Product> employeeList = db.Products.ToList<Product>();  
  23.                 return Json(new { data = employeeList }, JsonRequestBehavior.AllowGet);  
  24.             }  
  25.         }  
  26.   
  27.         [HttpPost]  
  28.         public ActionResult Upload(HttpPostedFileBase xmlFile)  
  29.         {  
  30.             if (xmlFile.ContentType.Equals("application/xml") || xmlFile.ContentType.Equals("text/xml"))  
  31.             {  
  32.                 var xmlPath = Server.MapPath("~/FileUpload" + xmlFile.FileName);  
  33.                 xmlFile.SaveAs(xmlPath);  
  34.                 XDocument xDoc = XDocument.Load(xmlPath);  
  35.                 List<Product> productList = xDoc.Descendants("product").Select  
  36.                     (product => new Product  
  37.                     {  
  38.                         Id = Convert.ToInt32(product.Element("id").Value),  
  39.                         Name = product.Element("name").Value,  
  40.                         Price = Convert.ToDecimal(product.Element("price").Value),  
  41.                         Quantity = Convert.ToInt32(product.Element("quantity").Value)  
  42.                     }).ToList();  
  43.   
  44.                 using (DBModel db = new DBModel())  
  45.                 {  
  46.                     foreach (var i in productList)  
  47.                     {  
  48.                         var v = db.Products.Where(a => a.Id.Equals(i.Id)).FirstOrDefault();  
  49.   
  50.                         if (v != null)  
  51.                         {  
  52.                             v.Id = i.Id;  
  53.                             v.Name = i.Name;  
  54.                             v.Price = i.Price;  
  55.                             v.Quantity = i.Quantity;  
  56.                         }  
  57.                         else  
  58.                         {  
  59.                             db.Products.Add(i);  
  60.                         }  
  61.                         db.SaveChanges();  
  62.                     }  
  63.                 }  
  64.                 ViewBag.Success = "File uploaded successfully..";  
  65.             }  
  66.             else  
  67.             {  
  68.                 ViewBag.Error = "Invalid file(Upload xml file only)";  
  69.             }  
  70.             return View("Index");  
  71.         }  
  72.     }  
  73. }  

Step 6

Create a folder to upload file with name FileUpload.

Step 7

Right click on index action method in controller. Add view window will appear with default index name unchecked (use a Layout page), and click on Add as shown in the below screenshot. View will be added in views folder under Home folder with name index.

Screenshot for adding view

SQL Server 

Step 8

Design view with HTML, cshtml and bootstrap 4 classes,

Complete index view code

  1. @{  
  2.     Layout = null;  
  3. }  
  4.   
  5. <!DOCTYPE html>  
  6.   
  7. <html>  
  8. <head>  
  9.     <meta name="viewport" content="width=device-width" />  
  10.     <title>Index</title>  
  11.     <link href="~/Content/bootstrap.min.css" rel="stylesheet" />  
  12.     <script src="~/scripts/jquery-3.3.1.min.js"></script>  
  13.     <script src="~/scripts/bootstrap.min.js"></script>  
  14.     <link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />  
  15.     <script src="~/scripts/jquery.dataTables.min.js"></script>  
  16.     <script src="~/scripts/dataTables.bootstrap4.min.js"></script>  
  17.     <script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.validate/1.12.0/jquery.validate.min.js"></script>  
  18.     <style type="text/css">  
  19.         .error {  
  20.             color: red;  
  21.             display: inline-block;  
  22.             margin-bottom: -57px !important;  
  23.             width: 100%;  
  24.         }  
  25.     </style>  
  26.     <script type="text/javascript">  
  27.         $(document).ready(function () {  
  28.             $('form').validate({  
  29.                 rules: {  
  30.                     xmlFile: {  
  31.                         required: true  
  32.                     }  
  33.                 },  
  34.                 messages: {  
  35.                     xmlFile: "Choose file to upload",  
  36.                 }  
  37.             })  
  38.             $('#dataTable').DataTable({  
  39.                 "ajax": {  
  40.                     "url""/Home/GetData",  
  41.                     "type""GET",  
  42.                     "datatype""json"  
  43.                 },  
  44.                 "columns": [  
  45.                     { "data""Id" },  
  46.                     { "data""Name" },  
  47.                     { "data""Price" },  
  48.                     { "data""Quantity" }  
  49.                 ]  
  50.             });  
  51.         });  
  52.     </script>  
  53. </head>  
  54. <body>  
  55.     <div class="container py-5">  
  56.         @using (Html.BeginForm("Upload""Home", FormMethod.Post, new { enctype = "multipart/form-data" }))  
  57.         {  
  58.             <div class="row">  
  59.                 <div class="col-sm-1 col-md-6 col-xs-12">  
  60.                     <h5 class="text-danger">@ViewBag.Error</h5>  
  61.                     <h5 class="text-success">@ViewBag.Success</h5>  
  62.                     <div class="form-group">  
  63.                         <label>Choose XML File:</label>  
  64.                         <div class="input-group">  
  65.                             <div class="custom-file">  
  66.                                 <input id="xmlFile" name="xmlFile" type="file" class="custom-file-input" />  
  67.                                 <label class="custom-file-label"></label>  
  68.                             </div>  
  69.                             <div class="input-group-append">  
  70.                                 <input type="submit" class="btn btn-outline-primary" value="Upload" />  
  71.                             </div>  
  72.                         </div>  
  73.                     </div>  
  74.                 </div>  
  75.             </div>  
  76.         }  
  77.         <table id="dataTable" class="table table-bordered table-striped">  
  78.             <thead>  
  79.                 <tr>  
  80.                     <th>ID</th>  
  81.                     <th>Name</th>  
  82.                     <th>Price</th>  
  83.                     <th>Quantity</th>  
  84.                 </tr>  
  85.             </thead>  
  86.         </table>  
  87.     </div>  
  88. </body>  
  89. </html>  

Step 9

Create an xml file with the name products.xml

  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <products>  
  3.   <product>  
  4.     <id>1</id>  
  5.     <name>Mobele Phone</name>  
  6.     <price>12000</price>  
  7.     <quantity>2</quantity>  
  8.   </product>  
  9.   <product>  
  10.     <id>2</id>  
  11.     <name>Hand Watch</name>  
  12.     <price>1200</price>  
  13.     <quantity>5</quantity>  
  14.   </product>  
  15.   <product>  
  16.     <id>3</id>  
  17.     <name>T-Shirt</name>  
  18.     <price>499</price>  
  19.     <quantity>2</quantity>  
  20.   </product>  
  21.   <product>  
  22.     <id>4</id>  
  23.     <name>Shoe</name>  
  24.     <price>999</price>  
  25.     <quantity>2</quantity>  
  26.   </product>  
  27.   <product>  
  28.     <id>5</id>  
  29.     <name>Jeans</name>  
  30.     <price>999</price>  
  31.     <quantity>2</quantity>  
  32.   </product>  
  33. </products>  

Step 10

Run Project ctrl+F5

Screenshot-1

SQL Server

 

Screenshot-2

SQL Server 

Screenshot-3

SQL Server 
Screenshot-4
SQL Server 
Screenshot-5
 
SQL Server 
Screenshot-6
SQL Server 
Conclusion

In this article I have explained how to import xml Meta data in SQL server using MVC 5 step by step. I hope it will help you in your project.