4 Replies Latest reply: Sep 26, 2013 3:55 AM by Etbin RSS

    Xml parshing and data extraction [V 10G]

    Thej

      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

          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

            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

              seconds later

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

                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