Introduction:
Here
I will explain how to pass or send xml
file
as a parameter to SQL stored procedure in asp.net
using C# and VB.NET.
Description:
In previous articles I explained read xml node
values and bind data to gridview, how to insert
and read data from xml in asp.net, Create online poll system with percentage graphs, Bind xml data to dropdownlist/gridview in asp.net . Now I will explain how to pass or send xml
file
as a parameter to SQL stored procedure in asp.net
using C# and VB.NET.
In situation I got requirement like read data from
xml file and send that xml file as parameter to store procedure. My XML File Name
as “Sample.xml” and that would
contains data like this
<?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>
|
Now
I need to 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>
|
After
that add following namespaces in codebehind
C# Code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
|
Now
add following code in code behind
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();
}
}
}
|
VB.NET Code
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Partial Class
Default2
Inherits System.Web.UI.Page
Protected Sub
Page_Load(ByVal sender As Object, ByVal e As
EventArgs)
End Sub
Protected Sub
btnSend_Click(ByVal sender As Object, ByVal e As
EventArgs)
Dim xmlreader As New XmlTextReader(Server.MapPath("Sample.xml"))
Dim ds As New DataSet()
ds.ReadXml(xmlreader)
xmlreader.Close()
If ds.Tables.Count <> 0 Then
Using con As New SqlConnection("Data
Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("prc_readxmldata",
con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@XMLdata",
SqlDbType.Xml).Value = ds.GetXml()
Dim da As New SqlDataAdapter(cmd)
Dim ds1 As New DataSet()
da.Fill(ds1)
gvDetails.DataSource = ds1
gvDetails.DataBind()
con.Close()
End Using
End If
End Sub
End Class
|
|
In above code I mentioned stored procedure “prc_readxmldata” now 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
|
Here @XMLdata.nodes('/users/user') this will select sub nodes inside of
nodes.
Once procedure created in your database run your
application and check your output
Demo
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
|
|
11 comments :
how to insert data into sqlserver table using xml file
good work good code thank you sharing
what's the sql server version have used to create the stored procedure ?
Nice One Suresh Garu :-) :->
how can i send more than one sms at time..can u give us query regarding this....
Cannot generate SSPI context.
give me solution of this error
thank you for posting this..it was very usefull
i have one doubt?
i have to create web application in that select xml file as dynamic, aftr that store that entire file in database table as a string,at last store row and columns with respective , and space separeted values? how?
plz help me.................
SIR WORKING SMOTHLY GOOD ARTICLE
Thank U...
Very nice article but how can i insert xml data into sqlsever table