How to parse XML file in Oracle SQL

Question

I downloaded an daily exchange file from the below website into the database. How do I parse following XML document using oracle SQL?

"http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote;currency=true?view=basic&format=json"

Answer 1

if you've really downloaded the XML version and not the JSON one, you can form the basic query like:

select /*+ cursor_sharing_exact */ id, classname, name, value
  from (select rownum id, classname, field_list
          from myxml mx cross join
               xmltable('/list/resources/resource'
                        passing xmltype(mx.mydoc)
                        columns classname  varchar2(100) path '@classname',
                        field_list xmltype path 'field'

                       )
       ) 
       cross join xmltable('/field'
                    passing field_list
                    columns
                    name varchar2(200) path '@name',
                    value varchar2(200) path '.'
                   )

this would give you output like:

SQL> select /*+ cursor_sharing_exact */ id, classname, name, value
  2    from (select rownum id, classname, field_list
  3            from myxml mx cross join
  4                 xmltable('/list/resources/resource'
  5                          passing xmltype(mx.mydoc)
  6                          columns classname  varchar2(100) path '@classname',
  7                          field_list xmltype path 'field'
  8                         )
  9         )
 10         cross join xmltable('/field'
 11                      passing field_list
 12                      columns
 13                      name varchar2(200) path '@name',
 14                      value varchar2(200) path '.'
 15                     )
 16  /

        ID CLASSNAME            NAME                 VALUE
---------- -------------------- -------------------- --------------------
         1 Quote                change               0.099976
         1 Quote                chg_percent          0.009367
         1 Quote                name                 USD/KRW
         1 Quote                price                1067.400024
         1 Quote                symbol               KRW=X
         1 Quote                ts                   1359020860
         1 Quote                type                 currency
         1 Quote                volume               0

you could PIVOT that of course.

with data as (select /*+ cursor_sharing_exact */ id, classname, name, value
                from (select rownum id, classname, field_list
                        from myxml mx cross join
                             xmltable('/list/resources/resource'
                                      passing xmltype(mx.mydoc)
                                      columns classname  varchar2(100) path '@classname',
                                      field_list xmltype path 'field'
                                     )
                     ) 
                     cross join xmltable('/field'
                                  passing field_list
                                  columns
                                  name varchar2(200) path '@name',
                                  value varchar2(200) path '.'
                                 ) ) select id, classname,
       max(case when name = 'change' then value end) change,
       max(case when name = 'chg_percent' then value end) chg_percent,
       max(case when name = 'name' then value end) name,
       max(case when name = 'price' then value end) price,
       max(case when name = 'symbol' then value end) symbol,
       max(case when name = 'ts' then value end) ts,
       max(case when name = 'type' then value end) type,
       max(case when name = 'volume' then value end) volume   
 from data  
group by id, classname 
order by id

a sample (shortened data): http://sqlfiddle.com/#!4/7b53c/1

Answer 2

If you want to parse JSON files the PL/JSON library can help you. Pl/JSON site