Varshini S
asked on
XML to SQL Table using c#
I have the below XML file.
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<EmployeeID>4</EmployeeID>
<FirstName>Rob</FirstName>
<MiddleName xsi:nil="true" />
<LastName>Walters</LastNam e>
</Employee>
<EmployeeDetail>
<EmployeeID>168</EmployeeI D>
<Salary>45000</Salary>
<DOJ>2014-02-19</DOJ>
<LT>12</LT>
</EmployeeDetail>
</Employees>
The above example has only one child table but other XML file has two or more child tables.
I got this XML file as file stream object and i have loop through all the child tables and insert or update my SQL table.
To achieve this task , i need to use SQL bulk insert and iDataReader.
If the record not exists need to
INSERT the row in SQL table
If the row already exists
UPDATE the existing row.
How to loop through XML file until end of the XML tag (end of last child table node) and store this information in iDatareader and use SQL bulk insert to update or insert the relevant SQL table ?
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<EmployeeID>4</EmployeeID>
<FirstName>Rob</FirstName>
<MiddleName xsi:nil="true" />
<LastName>Walters</LastNam
</Employee>
<EmployeeDetail>
<EmployeeID>168</EmployeeI
<Salary>45000</Salary>
<DOJ>2014-02-19</DOJ>
<LT>12</LT>
</EmployeeDetail>
</Employees>
The above example has only one child table but other XML file has two or more child tables.
I got this XML file as file stream object and i have loop through all the child tables and insert or update my SQL table.
To achieve this task , i need to use SQL bulk insert and iDataReader.
If the record not exists need to
INSERT the row in SQL table
If the row already exists
UPDATE the existing row.
How to loop through XML file until end of the XML tag (end of last child table node) and store this information in iDatareader and use SQL bulk insert to update or insert the relevant SQL table ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ryan Chong:
I will get the above XML object as filestream and using the filestream i have to loop through (using iDataReader) and insert to SQL table using SQL Bulk insert. This was the task assigned to me and i need to achieve this in the same way.
I will get the above XML object as filestream and using the filestream i have to loop through (using iDataReader) and insert to SQL table using SQL Bulk insert. This was the task assigned to me and i need to achieve this in the same way.
using the filestream i have to loop through (using iDataReader) and insert to SQL table using SQL Bulk insert.I would not suggest to do a looping to insert the records as it will kill the overall performance.
if the XML content is in filestream, you may want to save it into a physical file before you can use Bulk Insert to refer to the XML content.
Importing and Processing data from XML files into SQL Server tables
https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
ASKER
Thanks, as you said i am going to use staging table. Can i get the code for loop through filestream object (mentioned above) using iDataReader ?
Insert the xml string into a staging table so it's easier to manipulate. Then use XML functions to identify new vs changed records and do any INSERT/UPDATE's from the staging table.