In This Article I will Explain you how to insert data from XML file to Database using a Stored Procedure
Step 1:First Create a Employee Table-TB_Employee
CREATE TABLE [dbo].[TB_Employee]
(
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[EmpDept] [nvarchar](50) NOT NULL,
[Salary] [decimal](18, 0) NOT NULL,
[Name] [varchar](20) NULL
)
Step 2:Create a XML file inside your Project-Employee.xml
<?xml version="1.0" Encoding="utf-8"?>
<Employees>
<Employee>
<EmpDept>CSE</EmpDept>
<Salary>12000</Salary>
<Name>Samantha<Name>
</Employee>
<Employee>
<EmpDept>ISE</EmpDept>
<Salary>20000</Salary>
<Name>Kajal<Name>
</Employee>
</Employees>
Step 3: Add a Asp.Net Web Form and a button in that to send the XML file-
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnsendxml" runat="server" Text="Send XML" OnClick="btnsendxml_click" />
</div>
</form>
</body>
</html>
Step 5:Create a stored procedure in the SQL Server-Insert_From_Xml
create procedure Insert_From_Xml
(@XMLData xml)
as
Begin
insert into TB_Employee
select
t.value('(EmpDept/text())[1]','nvarchar(100)')AS EmpDept ,
t.value('(Salary/text())[1]','nvarchar(120)')AS Salary,
t.value('(Name/text())[1]','nvarchar(120)')AS Name
from
@XMLdata.nodes('/Employees/Employee')AS TempTable(t)
End
Step 4: Add the below C# Code in the Code behind page of Asp.net form-
protected void btnsendxml_click(object sender, EventArgs e)
{
XmlTextReader xmltextr = new XmlTextReader(Server.MapPath("Employee.xml"));
DataSet ds=new DataSet();
ds.ReadXml(xmltextr);
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("Insert_From_Xml", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@XMLData", SqlDbType.Xml).Value = ds.GetXml();
cmd.ExecuteNonQuery();
con.Close();
}
No comments:
Post a Comment