How to insert bulk record into database using SQL Server,C# and XML

If we have list of records then we can not process each record to insert into database because it is time taking process and it will degrade the performance.In this case we can use bulk insert. Suppose if we have bulk record in xml then we can easily dump that xml record into database.

Check the below code sample to insert bulk record.

Suppose this is sample XML string -

XML Data String

<?xml version='1.0' encoding='utf-8'?>
<Employees>
  <Emp>
    <EmpID>101</EmpID>
    <EmpName>Jitendra</EmpName>
    <Age>27</Age>
  </Emp>
  <Emp>
    <EmpID>102</EmpID>
    <EmpName>Ramesh</EmpName>
    <Age>30</Age>
  </Emp>
  <Emp>
    <EmpID>103</EmpID>
    <EmpName>Raj</EmpName>
    <Age>24</Age>
  </Emp>
 </Employees>
 
 Now check the below stored procedure –

Stored Procedure

This stored proceduer will take the XML string as input and it will process this XML to insert into table.

--Stored procedure to insert bulk record using XML

CREATE PROCEDURE [dbo].[SP_EmpBulkInsert]   
(    @XMLData text    )   
AS   
BEGIN   
 SET NOCOUNT ON   
 BEGIN TRANSACTION   
 Begin Try   

  DECLARE @XMLDocPointer INT   
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XMLData   

   INSERT INTO tab_Employees
   (EmpID, EmpName, Age)   
   SELECT EmpID,EmpName,Age  
   FROM OPENXML(@XMLDocPointer,'/Employees/Emp',2)   
   WITH  (EmpID INT,EmpName nvarchar(50),Age INT)    

   EXEC sp_xml_removedocument @XMLDocPointer   
   COMMIT TRANSACTION   
   Return 0;    
 End Try   
 Begin Catch   
   ROLLBACK TRANSACTION   
 End Catch   
END

In this stored procedure I am inserting data into tab_Employees table, based on column selection data will be inserted to this table. Now chek the below code to call this stored procedure from C# application.

C# Code-

//Code to insert bulk records
        private void btnBulkInsert_Click(object sender, EventArgs e)
        {
            try
            {
                //Code to insert bulk record using XML
                string strConnectionString = "Your connection string";
                //Getting XML string
                string strXML = GetXMLString();
               
                //Connecting to database
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
                {
                    conn.ConnectionString = Convert.ToString(strConnectionString);
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SP_EmpBulkInsert", conn);
                    cmd.Parameters.AddWithValue("@XMLData", strXML);
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Records inserted successfully.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

         //XML string to insert bulk record
        private string GetXMLString()
        {
            return  @"<?xml version='1.0' encoding='utf-8'?>
<Employees>
  <Emp>
    <EmpID>101</EmpID>
    <EmpName>Jitendra</EmpName>
    <Age>27</Age>
  </Emp>
  <Emp>
    <EmpID>102</EmpID>
    <EmpName>Ramesh</EmpName>
    <Age>30</Age>
  </Emp>
  <Emp>
    <EmpID>103</EmpID>
    <EmpName>Raj</EmpName>
    <Age>24</Age>
  </Emp>
 </Employees>";
        }

In above code, I am getting XML string using GetXMLString() method. After getting the XML string I am passing this string as parameter for stored procedure. Finally when this code will be executed then you will find the new records in tab_Employees table.

How to get index of node based on node value using XSLT?

While working with XML in some of the cases, we want to get the index or position of particular value. This can be easily done by using XSLT.

In this article, I am explaining - How to get index of node based on node value using XSLT?

Example-

Suppose, I have this XML data-

<ResponseData>
    <value>C</value>
    <value>A</value>
    <value>B</value>
</Response>

Now if I want to get the position of ‘A’ then It should return '2' .

How to get it-

This is small XSLT code to get this position-

<!--Declaring Variable with value-->
<xsl:variable name="TempData" select="A"/>
 
<!--XSLT code to get position of 'A', output will be 2 -->
<xsl:value-of select="count(//abc:ResponseData/abc:value[text()=$TempData]/preceding-sibling::*)+1" />

In above line I have declared one variable - 'TempData'
and using select I have assigned value -'A'


Now to get the position of 'A', I have used count() method.

Here this expression -

count(//abc:ResponseData/abc:value[text()=$TempData]/preceding-sibling::*)+1

Will return the count of preceding sibling of A, which will be position of 'A'.

Like 'A', we can also get the position of other Values ('B' and 'C'), for this we need to assign this value to 'TempData' variable.



Thanks

How to remove Node from XML File?

Removing any single node from XML file is an easy task. But when we go for delete more than one node (also Child Node) from XML file, based on some condition then you get problem. Generally we use looping to remove node from XML file, but the main problem is that-
 
After deleting one node, control comes out from loop, means even if we have written code to delete more than one XML node, it comes out from loop. 

Workaround-

To solve this problem, make recursive call to delete XML Node.

In this article, I am going to explain you- How you can overcome this problem?

Here I have written following function to solve this problem-

 //Recursive function to remove XML node with InnerText StrRemoveValue
        private void RemoveXMLNode(XmlNodeList MainParentNode, string StrRemoveValue)
        {
            try
            {
                for (int i = 0; i < MainParentNode.Count; i++)
                {
                    XmlNode node = MainParentNode[i];
                    //Remove XML node if it's InnerText is StrRemoveValue
                    if (node.InnerText == StrRemoveValue)
                    {
                        //getting parent of current node
                        XmlNode parentNode = node.ParentNode;
                        //Deleting node from parent node
                        parentNode.RemoveChild(node);

                        //Again calling RemoveXMLNode() to delete other xml node with InnerText is equal to StrRemoveValue

                        RemoveXMLNode(parentNode.ChildNodes, StrRemoveValue);
                    }
                    else
                    {
                        //Calling function for child nodes
                        RemoveXMLNode(node.ChildNodes, StrRemoveValue);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

In this function this is main line

    RemoveXMLNode(parentNode.ChildNodes, StrRemoveValue);

Here I am calling function recursively, because after using this line control doesn't come out from loop and it process code to remove other child nodes. I have called this function in button_Click() event like this- 

RemoveXMLNode(RootList,"15000"); 

Here I am deleting XML node based on condition - If InnerText of any node is '15000' then delete that node.

Here is code-

 private void btnRemove_Click(object sender, EventArgs e)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
              
                //Loading XML file
                xmlDoc.Load(@"c:\myxml.xml");
              
                //Getting root node from XML
                XmlNodeList RootList = xmlDoc.GetElementsByTagName("Emp");
              
                //Calling function to delete node
                //If any node has InnerText 15000 then that will be deleted
                RemoveXMLNode(RootList,"15000");
              
                //Again saving XML file
                xmlDoc.Save(@"c:\myxmlNew.xml");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Now done, after execution of this code modified XML file will be saved in this location-

c:\myxmlNew.xml


Thanks