Insert bulk data using XML serialization and XQuery

Suppose I have a grid showing the entries of all employees (ID, Name, Exp etc.) and I have to store the checked row’s ID , Name, Address, Contact to a different table. So 1000+ row checked at a time will be a common senario. So there is a very simple and efficient way to insert this bulk data into db with minimum overhead.

Here we are going to use XML serialization process to make a form XML atted string. Our desired xml should be look like,

001
Altair
Acre
001-334567


001
Sam Fisher
Splinter Cell
001-334568


001
Ezio Auditore
Florance
001-334569

......................................

Now we will make some classes for serialization purpose,

[XmlRoot("Employees")]
public class Employees
{
[XmlElement("Employee")]
public Employee[] employee;
}


[XmlRoot("Employee")]
public class Employee
{
public string ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Contact { get; set; }
}

 
The string insdie XmlRoot attribute will be the tag name,

So from client side all we have to do is to get the details of checked row and push them into array much like,

var data = new Array();

[Loop through grid and pick up the checked row]
data.push({
ID: ,
Name: ,
Address: ,
Contact : 
});

 
Now send this array to a service by converting to JSON object – JSON.stringify(data)

In the service you can use the “Employee” class to catch the data – use List emp

The bellow method is used for serialization purpose, this will return a string with xml format

===========================================================================

public static string GetSerializedXMLData(Type xmlClass, object xmlData)

{
try
{
XmlSerializer serialize = new XmlSerializer(xmlClass);
MemoryStream stream = new MemoryStream();

XmlWriterSettings settings = new XmlWriterSettings();
settings.OmitXmlDeclaration = true;

using (XmlWriter writer = XmlWriter.Create(stream, settings))
{
serialize.Serialize(writer, xmlData, xmlNamespace);

StreamReader reader = new StreamReader(stream);
writer.Flush();
stream.Position = 0;

return reader.ReadToEnd();
}
}
catch (Exception exGen)
{
_log.Error("Error in generating serialized xml data:" + exGen.Message, exGen);
}

return null;
}

 
============================================================================

We can call the method like the following,

Employees emps= new Employees();
emps.employee= emp.ToArray();
string xmlData = Utilities.GetSerializedXMLData(typeof(Employees), emps);

 
Now xmlData will contain a xml formatted string described above.

Now we will send this string data to SQL server and the XQuery will be,

=============================================================================

CREATE PROCEDURE [dbo].[spInsertEmployeeData]
@xmlData VARCHAR(MAX)
AS
BEGIN
DECLARE @xmlData XML,
@child XML,
@totalData INT,
@count INT = 1

SET @xmlData = @leauges 

-- Get the total number of data
SET @totalData = @xmlData.value('count(/Employees/Employee)','INT')

-- Loop through the data
WHILE @count