Re: Loading XML into Oracle db

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_at_unionbay.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
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

--
http://www.freelists.org/webpage/oracle-l