User260886948 posted
Hi,
Please try to refer to the following code:
The Sample.xml:
<?xml version="1.0" encoding="utf-8" ?>
<users>
<user>
<FirstName>Suresh</FirstName>
<LastName>Dasari</LastName>
<UserName>SureshDasari</UserName>
<Job>Team Leader</Job>
</user>
<user>
<FirstName>Mahesh</FirstName>
<LastName>Dasari</LastName>
<UserName>MaheshDasari</UserName>
<Job>Software Developer</Job>
</user>
<user>
<FirstName>Madhav</FirstName>
<LastName>Yemineni</LastName>
<UserName>MadhavYemineni</UserName>
<Job>Business Analyst</Job>
</user>
</users>
Then send this xml file as parameter to stored procedure for that first create xml file in your application (Right Click your application and select Add New Item >> Select XML file) and give name as “Sample.xml” and write following code
in your aspx page like this:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Send xml file as a parameter to SQL stored procedure in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSend" Text="Send XML to Database" runat="server" onclick="btnSend_Click" /><br /><br />
<b>Inserted Records Details</b> :
<asp:GridView ID="gvDetails" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
In the .cs:
protected void btnSend_Click(object sender, EventArgs e)
{
XmlTextReader xmlreader = new XmlTextReader(Server.MapPath("Sample.xml"));
DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
if (ds.Tables.Count != 0)
{
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("prc_readxmldata", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@XMLdata", SqlDbType.Xml).Value = ds.GetXml();
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds1=new DataSet();
da.Fill(ds1);
gvDetails.DataSource = ds1;
gvDetails.DataBind();
con.Close();
}
}
}
Then for the stored procedure “prc_readxmldata”, we need to create that stored procedure in database like as shown below:
CREATE PROCEDURE prc_readxmldata
(
@XMLdata XML
)
AS
BEGIN
SELECT
t.value('(FirstName/text())[1]','nvarchar(120)')AS FirstName ,
t.value('(LastName/text())[1]','nvarchar(120)')AS LastName,
t.value('(UserName/text())[1]','nvarchar(120)')AS UserName,
t.value('(Job/text())[1]','nvarchar(120)')AS Job
FROM
@XMLdata.nodes('/users/user')AS TempTable(t)
END
Hope it can help you.
Best Regards,
Amy Peng
