Monday, May 27, 2013

Save Xml bulk data from C# to SQL


  StringBuilder Group_data_Xml = new StringBuilder();
                Group_data_Xml.Append("");
lst = (List)(HttpContext.Current.Session["items"]);
                     foreach(var item in lst)
                     {
                         Group_data_Xml.Append("                          Group_data_Xml.Append(" Name =\"" + item.EmpName + "\"");
                         Group_data_Xml.Append(" Address =\"" + item.EmpAddress + "\"");
                     
                         Group_data_Xml.Append(" Salary =\"" + item.EmpSalary + "\">");
                    
                         Group_data_Xml.Append("
");
                     }
                     Group_data_Xml.Append("
");    string param = Group_data_Xml.ToString();

     _db.SaveBulk(param);
------------------------------------------------------------------------------------------------------

-- SP_SaveBulkData  '       '

CREATE PROCEDURE SP_SaveBulkData
    (
    @XmlData varchar(Max)
    )
AS
BEGIN
   
       DECLARE @xmlHandle int                 
 EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @XmlData  
 INSERT INTO tblEmployee
           ([EmpName]
           ,[EmpAddress]
           ,[EmpSalary])
     SELECT
           src.Name
           ,src.Address2
           ,src.Salary
                                  
           FROM OPENXML (@xmlHandle, '/Root/MyData', 1)                                     
 WITH (                                                             
  Name varchar(max)                                                   
 ,Address2 varchar(250)
 ,Salary float  
                                                                                  
 ) src             
END

No comments:

Post a Comment

fill in blanks

ALL Reading Blanks: Special All approaches aim to increase blood flow to areas of tension and to release painful knots opt1 muscle kn...