In this article I will explain how to pass XML file as parameter to Stored Procedure in C# and VB.Net.
SQL Server 2005 onwards we can pass a parameter of XML data type to the Stored Procedure and also we can easily parse the XML and extract its Attribute and Tag values.
Database
I have created a simple table named CustomerDetails whose schema is shown below.
Note: The SQL for creating the database is provided in the attached sample code.
HTML Markup
The HTML Markup consists of a FileUpload control and a Button for uploading the XML file.
<asp:FileUpload ID = "FileUpload1" runat = "server" />
<asp:Button ID="Button1" Text="Upload XML" runat="server" OnClick="UploadXML" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
The XML File
The following XML file (Customers.xml) file contains the records of Customers. The data is stored as attribute and within the tags.
<?xmlversion="1.0"standalone="yes"?>
<Customers>
<CustomerId ="1">
<Name>John Hammond</Name>
<Country>United States</Country>
</Customer>
<CustomerId = "2">
<Name>Mudassar Khan</Name>
<Country>India</Country>
</Customer>
<CustomerId ="3">
<Name>Suzanne Mathews</Name>
<Country>France</Country>
</Customer>
<CustomerId ="4">
<Name>Robert Schidner</Name>
<Country>Russia</Country>
</Customer>
</Customers>
The Stored Procedure for parsing XML data
The below stored procedure is accepting a parameter of type XML (which would be passed from the code behind). This XML object is parsed and the Attribute and Tag values are fetched and inserted into the Table.
The nodes function of the XML data type is uses XQuery expression to pull out the XML nodes from the XML, for this case I need to fetch the Customer nodes and hence the expression is i.e. /Customers/Customer where Customers is the Root Node and Customer is the child node.
Once the nodes are fetched we need to extract the attribute and tag Inner Text values. For fetching the Inner Text values between the Tags we need to make use of the values function.
The values function can read the Attribute as well as the Inner Text.
Attribute
In order to read the attribute we need to pass the name of the Attribute prefix with @ and its data type, in this example the attribute Id is fetched using Customer.value('@Id', 'INT').
Inner Text
In order to fetch the inner text we need to pass the name of the Tag and its data type. The Inner Text of the XML tag is fetched using text function and we also make use of an index [1] which means it should fetch only the first matched value.
Finally the values are inserted into the CustomerDetails table.
CREATE PROCEDURE [dbo].[InsertXML]
@xml XML
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO CustomerDetails
SELECT
Customer.value('@Id','INT') AS Id, --ATTRIBUTE
Customer.value('(Name/text())[1]','VARCHAR(100)') AS Name, --TAG
Customer.value('(Country/text())[1]','VARCHAR(100)') AS Country --TAG
FROM
@xml.nodes('/Customers/Customer')AS TEMPTABLE(Customer)
END
Uploading the XML file and Inserting data in database using Stored Procedure
When the Upload button is clicked, the XML file is uploaded and then saved to a folder. The file is then read and the XML string is passed as parameter to the Stored Procedure which inserts the XML file data into the CustomerDetails table.
C#
protected void UploadXML(object sender, EventArgs e)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string filePath = Server.MapPath("~/Uploads/") + fileName;
FileUpload1.SaveAs(filePath);
string xml = File.ReadAllText(filePath);
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("InsertXML"))
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xml", xml);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
VB.Net
Protected Sub UploadXML(sender As Object, e As EventArgs)
Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim filePath As String = Server.MapPath("~/Uploads/") & fileName
FileUpload1.SaveAs(filePath)
Dim xml As String = File.ReadAllText(filePath)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("InsertXML")
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@xml", xml)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub
![Insert (Upload) XML file Data into SQL Table using Stored Procedure in ASP.Net using C#, VB.NET]()
Downloads
