Create Procedure-------------
Create procedure SP_Test_XMLInsert
(
@xmlstr ntext,
@Status int output
)
as
begin
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into person
select xml.per_id,xml.per_name,xml.per_city
from OPENXML(@hDoc,N'//Master',2)
with(
per_id numeric(10) '@per_id',
per_name varchar(max) '@per_name',
per_city varchar(max) '@per_city'
)xml
exec sp_xml_removedocument @hDoc
select @Status=SCOPE_IDENTITY();
end
Add code on button click--------------------
-------------------------------------------------------------
protected void Save_Record(object sender, EventArgs e)
{
#region —- Input XML —-
XElement input_RQ = new XElement("Request");
#region —- Master Node —-
foreach (ListItem lst in chkBranchList.Items)
{
if (lst.Selected)
{
string str=lst.Value;
XElement xMaster = new XElement("Master", new XAttribute("per_id", "123"),
new XAttribute("per_name", "1234"),
new XAttribute("per_city", "Delhi"));
input_RQ.Add(xMaster);
}
}
#endregion
#endregion
#region ————- Insert————–
SqlConnection sqlCon = new SqlConnection();
sqlCon.ConnectionString = ConfigurationManager.ConnectionStrings["connn"].ConnectionString;
SqlCommand sqlCommand = new SqlCommand();
sqlCon.Open();
sqlCommand = new SqlCommand();
sqlCommand.CommandText = "SP_Test_XMLInsert";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlCon;
sqlCommand.Parameters.AddWithValue("@xmlstr", input_RQ.ToString());
SqlParameter outParam = new SqlParameter("@Status", SqlDbType.VarChar, 50);
outParam.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(outParam);
sqlCommand.ExecuteNonQuery();
string sMsg = sqlCommand.Parameters["@Status"].Value.ToString();
#endregion ———–Insert————–
}