C# .NET - Multiple insert into sql table using xml

Asked By Anandh Ramanujam on 14-Apr-11 03:58 AM
HI,

I am formatting xml string in a string builder like this:

<Root>
<ImpactID>1</ImpactID>
<ImpactData>abc</ImpactData>

<ImpactID>1</ImpactID>
<ImpactData>xyz</ImpactData>

<ImpactID>1</ImpactID>
<ImpactData>pqr</ImpactData>
</Root>

I want to pass this string to sql stored procedure, extract the data and insert into a table.

Can anybody help me in this regard.

Thanks.
TSN ... replied to Anandh Ramanujam on 14-Apr-11 04:55 AM
Hi...
 
Below code Iam using bulkcopy to directly import XMl to sqlserverdatabase...
 
 
DataSet employeeData = new DataSet();
reportData.ReadXml(Server.MapPath(”employee.xml”));
 
SqlConnection connection = new SqlConnection(”CONNECTION STRING”);
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = “emp_table”;
      
//if your DB col names don’t match your XML element names 100%
//then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(”employeeId”, “employee_id”);
sbc.ColumnMappings.Add(”Name”, “name”);
 
connection.Open();
//table 4 is the main table in this dataset
sbc.WriteToServer(reportData.Tables[0]);
 
connection.Close();
 
//remove the xml file

Hope this helps You...
TSN ... replied to Anandh Ramanujam on 14-Apr-11 04:58 AM
Hi...
Here iam using sqlBulkCopy to directly import data to sql from XML file...
 
DataSet EmployeeData = new DataSet();
reportData.ReadXml(Server.MapPath(”Employee.xml”));
 
SqlConnection connection = new SqlConnection(”CONNECTION STRING”);
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = “Employee_table”;
      
//if your DB col names don’t match your XML element names 100%
//then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(”EmployeeId”, “Employee_id”);
sbc.ColumnMappings.Add(”Name”, “Name”);
 
connection.Open();
//table 4 is the main table in this dataset
sbc.WriteToServer(EmployeeData.Tables[0]);
 
connection.Close();
 
//remove the xml file

Hop this helps you.....
Jatin Trikha replied to Anandh Ramanujam on 14-Apr-11 05:13 AM
Check this out
Jatin Trikha replied to Anandh Ramanujam on 14-Apr-11 05:13 AM
Check this out