Home » Developer & Programmer » JDeveloper, Java & XML » xml parse values (Oracle 11.2.0.2.0)
xml parse values [message #594822] Tue, 03 September 2013 07:12 Go to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I would like to read some values from XML code, I try to do some test sql query but not successfully.

The values what I need from xml are:
<currency>EUR</currency>
<amount>64.93</amount>



And there is a one example of xml:

 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>'); 




Please does anybody know how to write sql which get me a needed values?

Thanks a lot

Regards
Re: xml parse values [message #594827 is a reply to message #594822] Tue, 03 September 2013 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I try to do some test sql query but not successfully.
...
Please does anybody know how to write sql which get me a needed values?


Post what you already tried.
There are many examples in this forum to "extractvalue" from XML. (clue: this is a clue)

Regards
Michel

[Updated on: Wed, 04 September 2013 04:53]

Report message to a moderator

Re: xml parse values [message #594829 is a reply to message #594827] Tue, 03 September 2013 07:22 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
I tried to select these values from this query but not successfully:

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 

Re: xml parse values [message #594918 is a reply to message #594829] Wed, 04 September 2013 00:58 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

Please, does anybody know how to write it ?

Thanks
Re: xml parse values [message #594920 is a reply to message #594918] Wed, 04 September 2013 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are several "amount" and "currency" nodes, how can we (and Oracle) know which one you want?

Regards
Michel
Re: xml parse values [message #594922 is a reply to message #594920] Wed, 04 September 2013 01:33 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

I need to get all of these values from code:

<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>


The values "amount" and "currency" nodes should be selected from all appearances.
Re: xml parse values [message #594925 is a reply to message #594922] Wed, 04 September 2013 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand, this is not what you first posted.
What is the result you want from the data you gave?
What is the rule to select this result?

Regards
Michel
Re: xml parse values [message #594926 is a reply to message #594925] Wed, 04 September 2013 02:01 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

I first posted just about values:

<currency>EUR</currency>
<amount>64.93</amount>


And if somebody give me a right select how to do this then I use this one to another one values (as I wrote last).


Lets just talk about "amount" and "currency" nodes.
Re: xml parse values [message #594927 is a reply to message #594926] Wed, 04 September 2013 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 04 September 2013 08:14
There are several "amount" and "currency" nodes, how can we (and Oracle) know which one you want?

Regards
Michel

Re: xml parse values [message #594928 is a reply to message #594927] Wed, 04 September 2013 02:20 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member

Is it a possible to select all "amount" and "currency" nodes ?
Re: xml parse values [message #594932 is a reply to message #594928] Wed, 04 September 2013 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Everything is possible.
What are your requirements?

Regards
Michel
Re: xml parse values [message #594933 is a reply to message #594932] Wed, 04 September 2013 02:55 Go to previous messageGo to next message
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member


Simply select all values from "amount" and "currency" nodes.
Re: xml parse values [message #594939 is a reply to message #594933] Wed, 04 September 2013 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 03 September 2013 14:19
...
There are many examples in this forum to "extractvalue" from XML. (clue: this is a clue)

Regards
Michel

Re: xml parse values [message #595005 is a reply to message #594939] Wed, 04 September 2013 18:25 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following are various different syntaxes for doing what you asked. The first is an older syntax. The second is a newer syntax and is what I recommend in this situation. The third, fourth, and fifth are similar to what you seem to be trying to do. I believe the third, fourth, and fifth syntaxes are intended for more complex things where there is more nesting of values and are unnecessarily overcomplicated for what you are trying to do here. I posted them only so you could see the difference and test them if you like.

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.

Previous Topic: DestinaitonURL value with parameters
Next Topic: Parsing XML with special character as part of data
Goto Forum:
  


Current Time: Thu Mar 28 17:47:24 CDT 2024