if we have number of parameters. We can append all parameters to xml parameter and passing as a single parameter in xml. This if helpful for more performance to a stored procedure.
Follow the Below steps
Step-1: Create Table
Step-2: Create Procedure like below
CREATE PROCEDURE [dbo].[USP_XMLInsert]
(
@xmlx xml
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Tbl_Customer
SELECT
Customer.value(‘(Name/text())[1]’,’NVARCHAR(50)‘) as Name , –TAG
Customer.value(‘(City/text())[1]’,’NVARCHAR(50)‘) as City, –TAG
Customer.value(‘(IndustryType/text())[1]’,’NVARCHAR(50)‘) as IndustryType –TAG
FROM
@xmlx.nodes(‘/Customer‘)AS TEMPTABLE(Customer)
END
GO
Step 3: Create sample MVC application and create model class in models folder and create properties like below;
namespace Insert_XMLPROC.Models
{
public class Customer
{
public string Name { get; set; }
public string City { get; set; }
public string IndustryType { get; set; }
}
}
Step 4: Create controller in controller folder and write insert logic
public class CustomerController : Controller
{
// GET: Customer
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(Customer objcustomer)
{
StringBuilder objstring = new StringBuilder();
objstring.Append(“<Customer>“);
objstring.Append(“<Name>“);
objstring.Append(objcustomer.Name);
objstring.Append(“</Name>“);
objstring.Append(“<City>“);
objstring.Append(objcustomer.City);
objstring.Append(“</City>“);
objstring.Append(“<IndustryType>“);
objstring.Append(objcustomer.IndustryType);
objstring.Append(“</IndustryType>“);
objstring.Append(“</Customer>“);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(objstring.ToString());
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConnection“].ToString());
con.Open();
SqlCommand cmd = new SqlCommand(“USP_XMLInsert“, con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@xmlx“, xmlDoc.InnerXml);
cmd.ExecuteNonQuery();
return View();
}
}
Step 5: Create index view and write insert logic
@model Insert_XMLPROC.Models.Customer
@{
ViewBag.Title = “Index“;
}
<h2>Index</h2>
<script src=“~/Scripts/jquery-1.10.2.js”></script>
<script src=”~/Scripts/jquery-1.10.2.min.js“></script>
<div>
<table>
<tr>
<td>Name</td>
<td>
<input type=”text” id=”txtName” />
</td>
</tr>
<tr>
<td>City</td>
<td>
<input type=”text” id=”txtCity” />
</td>
</tr>
<tr>
<td>IndustryType</td>
<td>
<input type=”text” id=”txtIndustryType” />
</td>
</tr>
<tr>
<td>
<input type=”button” id=”btnsubmit” value=”Save” />
</td>
</tr>
</table>
<script type=“text/javascript”>
$(document).ready(function () {
$(‘#btnsubmit‘).click(function () {
$.ajax(
{
type: “POST“,
url: ‘/Customer/Index‘,
data: {
Name: $(‘#txtName‘).val(),
City: $(‘#txtCity‘).val(),
IndustryType: $(‘#txtIndustryType‘).val(),
}
});
});
});
</script>
</div>