Import Relational Xml Data into sql server 2012




<Order>
<AmazonOrderID>1111-222-33</AmazonOrderID>
<MerchantOrderID>111-222-33</MerchantOrderID>
<PurchaseDate>2014-08-03T18:11:11+00:00</PurchaseDate>
<LastUpdatedDate>2014-08-03T18:11:14+00:00</LastUpdatedDate>
<OrderStatus>Pending</OrderStatus>
<SalesChannel>Amazon.in</SalesChannel>
<FulfillmentData>
<FulfillmentChannel>Amazon</FulfillmentChannel>
<ShipServiceLevel>Standard</ShipServiceLevel>
<Address>
<City>bangalore</City>
<State>karnataka</State>
<PostalCode>560038</PostalCode>
<Country>IN</Country>
</Address>
</FulfillmentData>
<OrderItem>
<ASIN>B00AW9A53Q</ASIN>
<SKU>SiM13</SKU>
<ItemStatus>Unshipped</ItemStatus>
<ProductName>Chicco 500ml Body Lotion</ProductName>
<Quantity>1</Quantity>
<ItemPrice>
<Component>
<Type>Principal</Type>
<Amount currency="INR">390.0</Amount>
</Component>
</ItemPrice>
</OrderItem>
</Order>


how can i import this xml data into relational tables. i have 2 tables one is Order and another is items.i wanna insert below mentioned line in order table with as primary key.



<AmazonOrderID>111-222-333</AmazonOrderID>
<MerchantOrderID>1111-3333-444</MerchantOrderID>
<PurchaseDate>2014-08-03T18:11:11+00:00</PurchaseDate>
<LastUpdatedDate>2014-08-03T18:11:14+00:00</LastUpdatedDate>
<OrderStatus>Pending</OrderStatus>
<SalesChannel>Amazon.in</SalesChannel>


and order item in items table with amazonid as foreign key



<OrderItem>
<ASIN>B00AW9A53Q</ASIN>
<SKU>SiM13</SKU>
<ItemStatus>Unshipped</ItemStatus>
<ProductName>Chicco 500ml Body Lotion</ProductName>
<Quantity>1</Quantity>
<ItemPrice>
<Component>
<Type>Principal</Type>
<Amount currency="INR">390.0</Amount>
</Component>
</ItemPrice>
</OrderItem>
</Order>


please anyone can provide solution for this..i will be very grateful.


No comments:

Post a Comment