Re: Loading XML into Oracle db
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: eugene.pipko@xxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Jun 2011 08:59:26 -0700 (PDT)
Using 9.2.0.8 makes this a challenge but read here:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96621/adx01bas.htm#1011989 ;
It recommends using SQL*Loader and gives tips on how to create the loader
control file. Hopefully this will help.
David Fitzjarrell
________________________________
From: Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Mon, June 6, 2011 8:50:46 AM
Subject: Loading XML into Oracle db
Hi all,
Oracle 9.2.0.8 on Win 2003.
I need to load an XML file that is coming from Amazon.com site into Oracle
database. I’ve never done this before and looking for your help.
Ideally, (if possible) I think, I want to be able to parse this file and load
each element (ItemId, ItemTax, ShippingPrice ….) in its own table column.
If someone knows a good site to learn from, please share a link to it.
Here is the sample file:
<?xml version="1.0"?>
<ListOrderItemsResponse
xmlns="https://mws.amazonservices.com/Orders/2011-01-01";>
<ListOrderItemsResult>
<AmazonOrderId>305-3413222-1220037</AmazonOrderId>
<OrderItems>
<OrderItem>
<ASIN>B0050FWB6G</ASIN>
<OrderItemId>06782084248170</OrderItemId>
<SellerSKU>Y18WW31_173Y_29</SellerSKU>
<Title>SURVIVOR SHORT</Title>
<QuantityOrdered>1</QuantityOrdered>
<QuantityShipped>0</QuantityShipped>
<ItemPrice>
<CurrencyCode>USD</CurrencyCode>
<Amount>19.99</Amount>
</ItemPrice>
<ItemTax>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</ItemTax>
<ShippingPrice>
<CurrencyCode>USD</CurrencyCode>
<Amount>4.49</Amount>
</ShippingPrice>
<ShippingTax>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</ShippingTax>
<ShippingDiscount>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</ShippingDiscount>
<PromotionDiscount>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</PromotionDiscount>
</OrderItem>
<OrderItem>
<ASIN>BKF50FWBAA</ASIN>
<OrderItemId>06782084215425</OrderItemId>
<SellerSKU>Y18WW81_100Y_30</SellerSKU>
<Title>SURVIVOR PANT</Title>
<QuantityOrdered>1</QuantityOrdered>
<QuantityShipped>0</QuantityShipped>
<ItemPrice>
<CurrencyCode>USD</CurrencyCode>
<Amount>17.99</Amount>
</ItemPrice>
<ItemTax>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</ItemTax>
<ShippingPrice>
<CurrencyCode>USD</CurrencyCode>
<Amount>4.49</Amount>
</ShippingPrice>
<ShippingTax>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</ShippingTax>
<ShippingDiscount>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</ShippingDiscount>
<PromotionDiscount>
<CurrencyCode>USD</CurrencyCode>
<Amount>0.00</Amount>
</PromotionDiscount>
</OrderItem>
</OrderItems>
</ListOrderItemsResult>
<ResponseMetadata>
<RequestId>5df298d9-b6b7-4a89-a97f-42b8c971a553</RequestId>
</ResponseMetadata>
</ListOrderItemsResponse>
Thanks,
Eugene
Other related posts:
