RE: Loading XML into Oracle db

From: Marco Gralike <Marco.Gralike_at_amis.nl>
Date: Tue, 26 Jul 2011 16:08:52 +0200
Message-ID: <7DAF1D7F593F594DB5A66B87BF6EADF7137BA116F5_at_AMISNT07.AMIS.local>

See here: http://forums.oracle.com/forums/forum.jspa?forumID=34 (among other the FAQ link) or here: http://blog.gralike.com (XML Content tab)

---

Van: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] namens David Fitzjarrell [oratune_at_yahoo.com] Verzonden: maandag 6 juni 2011 17:59
Aan: eugene.pipko_at_unionbay.com; oracle-l_at_freelists.org Onderwerp: 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<http://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

Received on Tue Jul 26 2011 - 09:08:52 CDT