Last post May 05, 2011 10:44 PM by Decker Dong - MSFT
May 04, 2011 11:36 AM|inxss|LINK
I want some guidelines to implement a process, I want to parse a complex XML file and then store the data in relational database. I receive a large and complex XML file which contains all sorts of data like customer details (First Name, Last Name, Address
.. etc), product information(name of product, price, size, color), shipping and billing infomation (address, city, state, zip, etc) and other misc details. Also, there is a one to many relationship between the customer and products and also shipping information.
I have a database which has normalized tables like customer, product, shipping and billing. Now, I want to insert the data in the respective tables by generating primary keys and assign foreign keys to other tables. How is this possible using dataset or datatables?
Or is there any better technique to do it.
My idea - Convert the xml to dataset using XmlReader and then iterate using foreach table in the dataset. But then in this case it does not generate any foreign keys. I have to generate them programatically using some random logic.
I have attached a small part of my xml file.
<customer_address1>555 Mulberry Dr.</customer_address1>
<name>$5 off all orders over $5!</name>
<product_option_name>Price Discount Amount</product_option_name>
I would really appreciate your help.
May 04, 2011 12:20 PM|Shellymn|LINK
Guidelines provided by microsoft.. please refer this
May 05, 2011 11:26 AM|inxss|LINK
Thanks Shellymn for providing the guideline. I now got a clear understanding of XML structure. But i think it does not really answer my question if I can have foreign keys or primary keys in the dataset / datatable and have automatic relations generated
when I pull the xml file in the dataset / datatable, so that I can use that information to push the data in the realational database.
Thanks again for the help..
May 05, 2011 10:44 PM|Decker Dong - MSFT|LINK
My generic idea is that you should create two related tables (One-To-Many, linked with primary key and foreign key)...
And then use XmlTextReader or XDoucment (LINQ-TO-XML) to read out specific xml nodes' values and insert into different tables with different sqlCommand...