Bulk Copy Using XML / DB Insert Using XML
Bulk Copy Using XML

Most of the times we are sending large amount of data from our application to DB. We are having different options to update the bulk data. One way is XML parameter, that is we will send a large data in XML format and within the Database we will get in a Table format and stored it in DB.

The following example will describe,

BulkCopyXML.cs



protected void Page_Load(object sender, EventArgs e)
{
string sInputXML = "<XMLDatas><XMLData><LocationID>1</LocationID><System>FF</System>
<NoOfEmployees>28</NoOfEmployees></XMLData>" +
"<XMLData><LocationID>2</LocationID><System>Adjudication</System>
<NoOfEmployees>54</NoOfEmployees></XMLData>" +
"<XMLData><LocationID>1</LocationID><System>Viking</System>
<NoOfEmployees>100</NoOfEmployees></XMLData></XMLDatas>";

using(SqlConnection objCon = new SqlConnection(CommonBehavior.GetMainConnectionString()))
{
objCon.Open();
using (SqlCommand objCmd = new SqlCommand())
{

objCmd.CommandText = "usp_GetXML";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objCon;
SqlParameter objParam = new SqlParameter("@xmlInput",sInputXML);
objCmd.Parameters.Add(objParam);
objCmd.ExecuteNonQuery();
}
}
}


In DB, First we create a tblXmlData table,

CREATE TABLE [dbo].[tblXmlData](
          [LocationID] [tinyint] NOT NULL,
          [System] [varchar](20) NOT NULL,
          [NoOfEmployess] [int] NOT NULL
) ON [PRIMARY]

GO





And the we create the SP usp_GetXML,

/*
--select * from tblXMLData
Author    : Suresh Kumar N
Execution : usp_GetXML '<XMLDatas>
   <XMLData><LocationID>1</LocationID><System>FF</System>
<NoOfEmployees>28</NoOfEmployees></XMLData>
   <XMLData><LocationID>2</LocationID><System>Adjudication</System>
<NoOfEmployees>54</NoOfEmployees></XMLData>
    <XMLData><LocationID>1</LocationID><System>Viking</System>
<NoOfEmployees>100</NoOfEmployees></XMLData>
   </XMLDatas>'
*/
CREATE PROC usp_GetXML
(
@xmlInput XML
)
AS BEGIN
SET NOCOUNT ON    
SET ANSI_NULLS ON
SET ARITHABORT ON
         
DECLARE @TempTable TABLE (LocationID TinyInt, System varchar(20),NoOfEmployees int) 
         
INSERT INTO @TempTable(LocationID,System,NoOfEmployees)
SELECT
          XmlTable.Data.value('(./LocationID)[1]','tinyint'),
          XmlTable.Data.value('(./System)[1]','varchar(20)'),
          XmlTable.Data.value('(./NoOfEmployees)[1]','int')
FROM @xmlInput.nodes('//XMLData') AS XmlTable(Data)

INSERT INTO tblXMLData
SELECT LocationID,System,NoOfEmployees FROM @TempTable
END

In SP, XMLTable(Data) is a alias name and with the alias name we will get values of the defined columns. Defined Columns must be the name of the innermost tag.i.e insert values


Now, we will run the application, the XML inputs are getting inserted into DB. In this example I have send a initialized string, instead of this you may for the XML tag as your needs.
02:14 | Category: | 0 comments

Comments (0)

Post a Comment