Extract data from a XML and load it into a table

Question

How would I fetch the data from a XML (loaded into a Oracle table) and create a table to load that extracted data.

I'm trying to create a table, with columns as follows, the below mentioned columns are tags in the xml

ID,  Provider_Name,  Index, Provider_ID_description, Provider_ID
 1,  Provider_P107,  1,     GNRCN,                   GNRCN
 1,  Provider_P107,  2,     INDF1,                   INDF1
 2,  Provider_EGUT,  1,     EGUT,                    EGUT

The XML is:

    <?xml version="1.0" encoding="us-ascii"?>
<AuxiliaryType auxiliaryTypeId="1617309" base="Value list" hasImplementation="false" isShared="true" masteredIn="false" name="ID list" type="String">
    <AuxiliaryObject id="1" name="Provider_P107">
        <Row>
            <Index>1</Index>
            <Provider_ID_description>GNRCN</Provider_ID_description>
            <Provider_ID>GNRCN</Provider_ID>
        </Row>
        <Row>
            <Index>2</Index>
            <Provider_ID_description>INDF1</Provider_ID_description>
            <Provider_ID>INDF1</Provider_ID>
        </Row>
    </AuxiliaryObject>
    <AuxiliaryObject id="2" name="Provider_EGUT">
        <Row>
            <Index>1</Index>
            <Provider_ID_description>EGUT</Provider_ID_description>
            <Provider_ID>EGUT</Provider_ID>
        </Row>
    </AuxiliaryObject>
</AuxiliaryType>

I've tried using the following query:

SELECT w.ID,
       p.Name,
       s.Index,
       q.P_Desc,
       q.P_ID
from  traptabclob t
left join xmltable ('/AuxiliaryType/AuxiliaryObject/'
                   passing t.testclob
                   columns ID      XmlType path '/AuxiliaryType/AuxiliaryObject/@id',
                   columns Name    XmlType path '/AuxiliaryType/AuxiliaryObject/@name',
                   columns Index   XmlType path 'Row/Index',
                   columns P_Desc  varchar2(201) path 'Row/Provider_ID_description',
                   columns P_ID    varchar2(21) path 'Row/Provider_ID',
                   ) q
on (1=1)
  left join xmltable('/Index'
                      passing q.Index
                      columns Index number path '.') s
    on (1=1)
  left join  xmltable('/AuxiliaryType/AuxiliaryObject/'
                        passing q.ID
                        columns ID number path '.') w
    on (1=1)
  left join   xmltable('/AuxiliaryType/AuxiliaryObject/'
                        passing q.Name
                        columns Name varchar2(21) path '.') p
    on (1=1);

I tried this http://www.sqlfiddle.com/#!4/1a672/7/0. Here is the new where I am able to fetch more data http://www.sqlfiddle.com/#!4/1a672/8/0, but I'm still struggling. This does not work when I have more than one index or row. How to do it If I have the count of AuxiliaryObject id ?

http://stackoverflow.com/questions/12982687/oracle-plsql-how-to-parse-xml-and-insert-into-table wont help you? as simple approach — Maheswaran Ravisankar, Jan 01 '14 at 06:39
With the above help I tried to do this :http://www.sqlfiddle.com/#!4/1a672/3/0 — Kimi, Jan 01 '14 at 13:30
I am able to fetch more data : http://www.sqlfiddle.com/#!4/1a672/8/0. However still cannot handle multiple INDEX — Kimi, Jan 01 '14 at 14:43
Hi, I've edited your question so that it flows slightly better. There's still room for a lot of improvement... I still don't quite understand what your problem is either. — Ben, Jan 01 '14 at 17:14
hi , The problem is I am not able to extract the data based on the different "AuxiliaryObject id="" name=" and different Index. I need to extract this xml and load this data into a table . The table should look like this sqlfiddle.com/#!4/1a672/8/0 — Kimi, Jan 01 '14 at 17:21
Please find the correct answer of the above on this link http://stackoverflow.com/questions/20899819/extract-xml-data-using-oracle-query — Kimi, Jan 04 '14 at 08:21

Extract data from a XML and load it into a table

0 Answers0

Linked