This discussion is archived
2 Replies Latest reply: Nov 21, 2012 11:30 PM by 974276 RSS

loading xml file using owb

974276 Newbie
Currently Being Moderated
Hi Gurus,

I am new to owb and as per requirement we need to load xml files into oracle table using owb.

below is the xml file:

<bookstore>
<book category="COOKING">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="CHILDREN">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>

please help me in loading above xml file using owb.
  • 1. Re: loading xml file using owb
    David Allan Guru
    Currently Being Moderated
    You can leverage the XML SQL functions to extract from XML using the database, see the blog post below;
    https://blogs.oracle.com/warehousebuilder/entry/leveraging_xdb


    For example to extract information from your XML document the following SQL can be generated from OWB;
    select extractValue(value(s), '/book/author'),
    extractValue(value(s), '/book/year'),
    extractValue(value(s), '/book/price') from
    ( select XMLType('<bookstore>
    +<book category="COOKING">+
    +<title lang="en">Everyday Italian</title>+
    +<author>Giada De Laurentiis</author>+
    +<year>2005</year>+
    +<price>30.00</price>+
    +</book>+
    +<book category="CHILDREN">+
    +<title lang="en">Harry Potter</title>+
    +<author>J K. Rowling</author>+
    +<year>2005</year>+
    +<price>29.99</price>+
    +</book>+
    +<book category="WEB">+
    +<title lang="en">Learning XML</title>+
    +<author>Erik T. Ray</author>+
    +<year>2003</year>+
    +<price>39.95</price>+
    +</book>+
    +</bookstore>') adoc from dual+) r,
    table(XMLSequence(extract(r.adoc, '/bookstore/book'))) s;


    Cheers
    David
  • 2. Re: loading xml file using owb
    974276 Newbie
    Currently Being Moderated
    Hi All,

    How to load nested xml's in owb?

    I am new to OWB .... Please explain the steps so that i can follow :-(

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points