Solved

Best approach to convert XML to SQL 2008 Tables/Fields using C#

Posted on 2011-09-19
4
355 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:alkabello
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 100 total points
ID: 36562162
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.
0
 
LVL 2

Author Comment

by:alkabello
ID: 36566613
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?
0
 
LVL 2

Accepted Solution

by:
alkabello earned 0 total points
ID: 36573274
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.
0
 
LVL 2

Author Closing Comment

by:alkabello
ID: 36597719
The link for the SqlBulkCopy provided general information, but lacks specifics to address this specific need.

Low responses to this question makes it difficult to distribute points.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Express or Standard? 5 61
Web page design problem 3 41
Adding columns to a table which has non null columns 3 41
C# LINQ query question 11 30
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question