CREATE TABLE "XML_TAB" 
   (	"ID" NUMBER(10,0), 
	"FILENAME" VARCHAR2(100 BYTE), 
	"XML" "XMLTYPE"
   )

 Insert into XML_TAB (ID,FILENAME,XML) values ('1','2610748023_20120102.xml','<?xml version = ''1.0'' encoding = ''UTF-8'' standalone = ''no''?><?xml-stylesheet type="text/xsl" href="http://moja.tatrabanka.sk/ibanking/xsl/ikey-print.xsl"?>
<?iban-code SK33 1100 0000 0026 1074 8023?>
<tb_types:daily_statement xmlns:tb_types="http://www.tatrabanka.sk/tb_types" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.tatrabanka.sk/tb_types statement.xsd">
   <statement>
      <movements>
         <movement>
            <code>41100</code>
            <type>11</type>
            <id>0000104667268</id>
            <payer>
               <account_number>5222503001</account_number>
               <bank_code>5600</bank_code>
            </payer>
            <beneficiary>
               <account_name>MaPe Martin, Ing.</account_name>
               <account_number>2610748023</account_number>
               <bank_code>1100</bank_code>
            </beneficiary>
            <currency>EUR</currency>
            <amount>64.93</amount>
            <origination_date>2012-01-02</origination_date>
            <value_date>2012-01-02</value_date>
            <const_sym>138</const_sym>
            <db_var_sym>9</db_var_sym>
            <db_title>OSTROV OBEC</db_title>
            <db_info></db_info>
            <cr_var_sym>9</cr_var_sym>
            <cr_title></cr_title>
            <cr_info></cr_info>
            <msg_for_bank></msg_for_bank>
            <msg_for_beneficiary></msg_for_beneficiary>
            <posting_narrative>CC   5600   /000000-5222503001</posting_narrative>
         </movement>
         <movement>
            <code>51400</code>
            <type>11</type>
            <id>000590A555302</id>
            <payer>
               <account_name>MaPe Martin, Ing.</account_name>
               <account_number>2610748023</account_number>
               <bank_code>1100</bank_code>
            </payer>
            <beneficiary>
               <account_number>0000000000</account_number>
               <bank_code>0000</bank_code>
            </beneficiary>
            <currency>EUR</currency>
            <amount>17.34</amount>
            <origination_date>2012-01-02</origination_date>
            <value_date>2012-01-02</value_date>
            <db_title></db_title>
            <db_info>440577******9578   STRANI                                             281211000000 000000000044000CZK511 STRANI</db_info>
            <cr_title></cr_title>
            <cr_info></cr_info>
            <msg_for_bank>VE INT KUP POS</msg_for_bank>
            <msg_for_beneficiary></msg_for_beneficiary>
            <posting_narrative>VE INT KUP POS</posting_narrative>
         </movement>
      </movements>
      <checksum>
         <item>
            <hds>10th</hds>
            <cnt>2</cnt>
            <amount>82.27</amount>
         </item>
      </checksum>
      <info>
         <seq>1</seq>
         <input_date>2012-01-02</input_date>
         <account>
            <account_name>MaPe Martin, Ing.</account_name>
            <account_number>2610748023</account_number>
            <bank_code>1100</bank_code>
         </account>
         <currency>EUR</currency>
         <old_balance>-129.88</old_balance>
         <new_balance>-82.29</new_balance>
         <db_sum>17.34</db_sum>
         <cr_sum>64.93</cr_sum>
      </info>
   </statement>
</tb_types:daily_statement>'); 

select t2.X1_VALUE2,t.id, t.filename
from xml_tab t, 
     XMLTABLE(XMLNAMESPACES(default 'http://www.tatrabanka.sk/tb_types'),
                   'for $i in /*
                      return <dtp name="{$i/name()}">
                              {
                                for $j in $i/*
                                return $j
                              }
                             </dtp>'
                   passing t.xml
                   columns
X1_VALUE2 varchar2(50) PATH '/dtp/statement/movements/movement/code[@name]/text/text()'
  ) t2 

SCOTT@orcl12c> COLUMN currency FORMAT A8
SCOTT@orcl12c> SELECT EXTRACTVALUE (VALUE (t2), '/movement/currency') currency,
  2  	    TO_NUMBER (EXTRACTVALUE (VALUE (t2), '/movement/amount')) amount
  3  FROM   xml_tab t,
  4  	    TABLE (XMLSEQUENCE (EXTRACT (t.xml, '//statement/movements/movement'))) t2
  5  /

CURRENCY     AMOUNT
-------- ----------
EUR           64.93
EUR           17.34

2 rows selected.

SCOTT@orcl12c> SELECT t2.currency, t2.amount
  2  FROM   xml_tab t,
  3  	    XMLTABLE
  4  	      ('//statement/movements/movement'
  5  	       PASSING t.xml
  6  	       COLUMNS
  7  		 currency VARCHAR2(8) PATH 'currency',
  8  		 amount   NUMBER      PATH 'amount') t2
  9  /

CURRENCY     AMOUNT
-------- ----------
EUR           64.93
EUR           17.34

2 rows selected.

SCOTT@orcl12c> SELECT t2.currency, t2.amount
  2  FROM   xml_tab t,
  3  	    XMLTABLE
  4  	      ('for $i in //statement/movements/movement return $i'
  5  	       PASSING t.xml
  6  	       COLUMNS
  7  		 currency VARCHAR2(8) PATH 'currency',
  8  		 amount   NUMBER      PATH 'amount') t2
  9  /

CURRENCY     AMOUNT
-------- ----------
EUR           64.93
EUR           17.34

2 rows selected.

SCOTT@orcl12c> SELECT t2.currency, t2.amount
  2  FROM   xml_tab t,
  3  	    XMLTABLE
  4  	      ('for $i in //statement/movements/movement return
  5  		<dtp>
  6  		  {$i/currency}
  7  		  {$i/amount}
  8  		</dtp>'
  9  	       PASSING t.xml
 10  	       COLUMNS
 11  		 currency VARCHAR2(8) PATH 'currency',
 12  		 amount   NUMBER      PATH 'amount') t2
 13  /

CURRENCY     AMOUNT
-------- ----------
EUR           64.93
EUR           17.34

2 rows selected.

SCOTT@orcl12c> SELECT t2.currency, t2.amount
  2  FROM   xml_tab t,
  3  	    XMLTABLE
  4  	      ('for $i in //statement/movements
  5  		  for $j in $i/* return $j'
  6  	       PASSING t.xml
  7  	       COLUMNS
  8  		 currency VARCHAR2(8) PATH 'currency',
  9  		 amount   NUMBER      PATH 'amount') t2
 10  /

CURRENCY     AMOUNT
-------- ----------
EUR           64.93
EUR           17.34

2 rows selected.