Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Pass XML as Parameter from C# to SQL Stored Procedure in VB.NET

Dec 13, 2012
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 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 RSS subscribe by email Subscribe by Email

23 comments :

Anonymous said...

how to insert data into sqlserver table using xml file

dizüstü bilgisayar said...

good work good code thank you sharing

Anonymous said...

what's the sql server version have used to create the stored procedure ?

Unknown said...

Nice One Suresh Garu :-) :->

saurabh pandey said...

how can i send more than one sms at time..can u give us query regarding this....

Unknown said...

Cannot generate SSPI context.
give me solution of this error

Anonymous said...

thank you for posting this..it was very usefull

Anonymous said...

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.................

DotNet said...

SIR WORKING SMOTHLY GOOD ARTICLE

Unknown said...

Thank U...

Unknown said...

Very nice article but how can i insert xml data into sqlsever table

Anonymous said...

what is t in sotred procedure

ammukarthi said...

how to create xml files using a database table using vb.net

buzdolapları said...

Nice job. I'm beginner but i try to learn c#. Thanks for your posting.

Anonymous said...

GUD

Unknown said...

How Can I Insert Xml Structure in Single Column in the Table.. Thanks in Advance

Unknown said...

Good...Keep coding....

Nitin Kumar said...

How can I Insert/Update Huge Data (almost 10000 records) from Excel file to Database Table after validating each record from database without using Bulk copy? I have 26 columns in my Excel File.
Thanks in advance....

Unknown said...

great Article sir.
Can you please post also How to update data from xml file same way.
I have one table and if record already stored in table i need to update that row base on my one column is unique,
for Example as per your table my first name its unique.. So if same name is store than update table from xml

Srinivas said...

Thanks for posting this sir...

Anonymous said...

Dear Sir

How i upload this below format xml file in Sql server-2008
















Anonymous said...

good work... Thanks for sharing this.. working

venu said...

how to pass or send xml file as a parameter to SQL stored procedure in php??

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.