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