2 Replies Latest reply: Nov 22, 2012 1:30 AM by 974276 RSS

    loading xml file using owb

    974276
      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-Oracle
          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
            Hi All,

            How to load nested xml's in owb?

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