The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Solved
Best approach to convert XML to SQL 2008 Tables/Fields using C#
Posted on 2011-09-19
I’m looking for the best approach to convert a XML data stream (see attached sample) into records for various SQL 2008 tables using C#. This XML stream will be provided as a web service, and will be the result of calling the service with a start date and end date. All orders modified during that time window will be included in the XML stream. Attached please find the WSDL for this XML data.
Due to the structure of the XML, I’ve created a number of tables in SQL with the following 'rules':
- For the Order Header portion, the quote number will be OrderKey.
- For the Order Detail, in addition to the OrderKey, the line number will be LineKey.
- For two of the Detail files, I’ve added IndexKey which will be a sequential number starting at 1 for each line.
- The Order Header also contains two ‘sub elements’. These are Address and Contact as each of these items are applied EndCustomer, JobSite etc. So, for these two tables, I’ve added a field called ElementKey to identify what the record references.
The expectation is that this data will remain in the SQL tables, and various data will be copied to other Business systems like Great Plains. Is the best approach to ‘pull’ field values from the XML, and write records to SQL, or can some type of ‘translation’ be created to map XML to tables/fields? If so, please provide the C# code needed for a few of these fields.
Thanks in advance for your suggestions.
Orders-Single.xml orderservice.xml Order-Header-Diagram.pdf
Order-Detail-Diagram.pdf
Due to the structure of the XML, I’ve created a number of tables in SQL with the following 'rules':
- For the Order Header portion, the quote number will be OrderKey.
- For the Order Detail, in addition to the OrderKey, the line number will be LineKey.
- For two of the Detail files, I’ve added IndexKey which will be a sequential number starting at 1 for each line.
- The Order Header also contains two ‘sub elements’. These are Address and Contact as each of these items are applied EndCustomer, JobSite etc. So, for these two tables, I’ve added a field called ElementKey to identify what the record references.
The expectation is that this data will remain in the SQL tables, and various data will be copied to other Business systems like Great Plains. Is the best approach to ‘pull’ field values from the XML, and write records to SQL, or can some type of ‘translation’ be created to map XML to tables/fields? If so, please provide the C# code needed for a few of these fields.
Thanks in advance for your suggestions.
Orders-Single.xml orderservice.xml Order-Header-Diagram.pdf
Order-Detail-Diagram.pdf
-
3
4 Comments
You can load your XML data into DataTables then use the SqlBulkCopy class to copy the data into the SQL tables.
SqlBulkCopy Class (System.Data.SqlClient)
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
I hope this helps.
SqlBulkCopy Class (System.Data.SqlClient)
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
I hope this helps.
What are the advantages of using SqlBulkCopy compare to using XMLreader or the DataSet approach.
How would SqlBulkCopy be used to:
- Populate 1 record per Order_Id from the XML into the multiple tables, and then 'n' records per Order_Id in tables like Order_LineItem and Order_LineItem_TextDetails
- Assign values to the key fields in each table (ie OrderKey in table Order_Header will use QuoteNumber from the XML)?
- Handle exceptions like the OrderKey already existing?
How would SqlBulkCopy be used to:
- Populate 1 record per Order_Id from the XML into the multiple tables, and then 'n' records per Order_Id in tables like Order_LineItem and Order_LineItem_TextDetails
- Assign values to the key fields in each table (ie OrderKey in table Order_Header will use QuoteNumber from the XML)?
- Handle exceptions like the OrderKey already existing?
I'm focusing my efforts on using Datasets based on the XML scheme.
I will close this question since the low response may be due to an overly broad question.
I will close this question since the low response may be due to an overly broad question.
Featured Post
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month7 days, 13 hours left to enroll
585 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.