This discussion is archived
4 Replies Latest reply: Sep 26, 2013 1:55 AM by Etbin RSS

Xml parshing and data extraction [V 10G]

Thej Newbie
Currently Being Moderated

Hi Experts,

 

I have a XML CD Catalog file. I have to extract the values of fields in xml and load it into a table of same no of columns.

 

can Some one help me out How can i extract the data from XML

 

XMl sample

--------------------

<!-- Edited by XMLSpy® -->

<CATALOG>

<CD><TITLE>Empire Burlesque</TITLE><ARTIST>Bob Dylan</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>Columbia</COMPANY><PRICE>10.90</PRICE><YEAR>1985</YEAR></CD>

<CD><TITLE>Hide your heart</TITLE><ARTIST>Bonnie Tyler</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>CBS Records</COMPANY><PRICE>9.90</PRICE><YEAR>1988</YEAR></CD>

<CD><TITLE>Greatest Hits</TITLE><ARTIST>Dolly Parton</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>RCA</COMPANY><PRICE>9.90</PRICE><YEAR>1982</YEAR></CD>

<CD><TITLE>Still got the blues</TITLE><ARTIST>Gary Moore</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>Virgin records</COMPANY><PRICE>10.20</PRICE><YEAR>1990</YEAR></CD>

<CD><TITLE>Eros</TITLE><ARTIST>Eros Ramazzotti</ARTIST><COUNTRY>EU</COUNTRY><COMPANY>BMG</COMPANY><PRICE>9.90</PRICE><YEAR>1997</YEAR></CD

</CATALOG>

 

Thanks

-----------------

Thej

  • 1. Re: Xml parshing and data extraction [V 10G]
    Etbin Guru
    Currently Being Moderated

    with

    xml_source as

    (select q'{

    <CATALOG>

    <CD><TITLE>Empire Burlesque</TITLE><ARTIST>Bob Dylan</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>Columbia</COMPANY><PRICE>10.90</PRICE><YEAR>1985</YEAR></CD>

    <CD><TITLE>Hide your heart</TITLE><ARTIST>Bonnie Tyler</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>CBS Records</COMPANY><PRICE>9.90</PRICE><YEAR>1988</YEAR></CD>

    <CD><TITLE>Greatest Hits</TITLE><ARTIST>Dolly Parton</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>RCA</COMPANY><PRICE>9.90</PRICE><YEAR>1982</YEAR></CD>

    <CD><TITLE>Still got the blues</TITLE><ARTIST>Gary Moore</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>Virgin records</COMPANY><PRICE>10.20</PRICE><YEAR>1990</YEAR></CD>

    <CD><TITLE>Eros</TITLE><ARTIST>Eros Ramazzotti</ARTIST><COUNTRY>EU</COUNTRY><COMPANY>BMG</COMPANY><PRICE>9.90</PRICE><YEAR>1997</YEAR></CD>

    </CATALOG>}' the_xml

      from dual

    )

    select x.*

      from xml_source s,

           xmltable('/CATALOG/CD'

                    passing xmltype(s.the_xml)

                    columns "TITLE"   varchar2(30) path 'TITLE',

                            "ARTIST"  varchar2(30) path 'ARTIST',

                            "COUNTRY" varchar2(5)  path 'COUNTRY',

                            "COMPANY" varchar2(30) path 'COMPANY',

                            "PRICE"   number       path 'PRICE',

                            "YEAR"    number       path 'YEAR'

                   ) x

     

    TITLEARTISTCOUNTRYCOMPANYPRICEYEAR
    Empire BurlesqueBob DylanUSAColumbia10.91985
    Hide your heartBonnie TylerUKCBS Records9.91988
    Greatest HitsDolly PartonUSARCA9.91982
    Still got the bluesGary MooreUKVirgin records10.21990
    ErosEros RamazzottiEUBMG9.91997

     

    Regards

     

    Etbin

  • 2. Re: Xml parshing and data extraction [V 10G]
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    with t as(

    select '<CATALOG>

    <CD><TITLE>Empire Burlesque</TITLE><ARTIST>Bob Dylan</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>Columbia</COMPANY><PRICE>10.90</PRICE><YEAR>1985</YEAR></CD>

    <CD><TITLE>Hide your heart</TITLE><ARTIST>Bonnie Tyler</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>CBS Records</COMPANY><PRICE>9.90</PRICE><YEAR>1988</YEAR></CD>

    <CD><TITLE>Greatest Hits</TITLE><ARTIST>Dolly Parton</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>RCA</COMPANY><PRICE>9.90</PRICE><YEAR>1982</YEAR></CD>

    <CD><TITLE>Still got the blues</TITLE><ARTIST>Gary Moore</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>Virgin records</COMPANY><PRICE>10.20</PRICE><YEAR>1990</YEAR></CD>

    <CD><TITLE>Eros</TITLE><ARTIST>Eros Ramazzotti</ARTIST><COUNTRY>EU</COUNTRY><COMPANY>BMG</COMPANY><PRICE>9.90</PRICE><YEAR>1997</YEAR></CD>

    </CATALOG>' C_xml from dual

    )

    select x.* from t,

    xmltable('/CATALOG/CD' passing xmltype(t.C_xml)

                    columns "TITLE" varchar2(4000) path '/CD/TITLE',

                            "ARTIST" varchar2(4000) path '/CD/ARTIST',

                            "COUNTRY" varchar2(4000) path '/CD/COUNTRY',

                            "COMPANY" varchar2(4000) path '/CD/COMPANY',

                            "PRICE" varchar2(4000) path '/CD/PRICE',      

                            "YEAR" varchar2(4000) path '/CD/YEAR'                                                                                       

                   ) x

     

    TITLEARTISTCOUNTRYCOMPANYPRICEYEAR
    Empire BurlesqueBob DylanUSAColumbia10.901985
    Hide your heartBonnie TylerUKCBS Records9.901988
    Greatest HitsDolly PartonUSARCA9.901982
    Still got the bluesGary MooreUKVirgin records10.201990
    ErosEros RamazzottiEUBMG9.901997

     

     

    ----

    Ramin Hashimzade

  • 3. Re: Xml parshing and data extraction [V 10G]
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    seconds later

  • 4. Re: Xml parshing and data extraction [V 10G]
    Etbin Guru
    Currently Being Moderated

    Might be because I used the pattern of some yesterday's (actually today's ) post of mine: https://forums.oracle.com/thread/2586055


    Regards

     

    Etbin

Legend

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