Home » Developer & Programmer » JDeveloper, Java & XML » XML parsing in Oracle (Oracle 11g)
XML parsing in Oracle [message #572870] Tue, 18 December 2012 06:04 Go to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Hi i am executing the following xml script

XML Script:
create or replace
PROCEDURE xmltest IS

v_xml_doc xmltype;

cursor c is select v_xml_doc.extract('/curr/branchid/@value').getStringVal() brchid,
v_xml_doc.extract('/curr/branchid/prodid/@value').getStringVal() prodid,
v_xml_doc.extract('/curr/branchid/prodid/currency/currid/text()').getstringval() currid,
v_xml_doc.extract('/curr/branchid/prodid/currency/dmltype/text()').getstringval() dmltype,
v_xml_doc.extract('/curr/branchid/prodid/currency/comments/text()').getstringval() comments
FROM TABLE (XMLSEQUENCE (v_xml_doc.extract ('/curr')));


BEGIN

v_xml_doc :=XMLTYPE('<curr>
<branchid value = "123">
<prodid value = "1234">
<currency>
<currid>USD</currid>
<dmltype>A</dmltype>
<comments>Added</comments>
</currency>
<currency>
<currid>EUR</currid>
<dmltype>D</dmltype>
<comments>Deleted</comments>
</currency>
<currency>
<currid>INR</currid>
<dmltype>A</dmltype>
<comments>Added</comments>
</currency>
<currency>
<currid>SGD</currid>
<dmltype>A</dmltype>
<comments>Added</comments>
</currency>
</prodid>
</branchid>
</curr>');

FOR c_rec in c
LOOP
dbms_output.put_line (c_rec.brchid||','||c_rec.prodid||','||c_rec.currid||','|| c_rec.dmltype||','|| c_rec.comments);
END LOOP;

END xmltest;

the output i am getting is:
123,6359,USDEURINRSGD,ADAA,AddedDeletedAddedAdded
but i want the output as:
123, 6359, USD, A, Added
123, 6359, EUR, D, Deleted
123, 6359, INR, A, Added
123, 6359, SGD, A, Added

Can anyone help??

[Updated on: Tue, 18 December 2012 06:07]

Report message to a moderator

Re: XML parsing in Oracle [message #572883 is a reply to message #572870] Tue, 18 December 2012 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

SQL> col brchid format a7
SQL> col prodid format a7
SQL> col currid format a7
SQL> col dmltype format a7
SQL> col comments format a8
SQL> with data as ( select XMLTYPE('<curr>
  2  <branchid value = "123">
  3   <prodid value = "1234">
  4    <currency>
  5     <currid>USD</currid>
  6     <dmltype>A</dmltype>
  7     <comments>Added</comments>
  8    </currency>
  9    <currency>
 10     <currid>EUR</currid>
 11     <dmltype>D</dmltype>
 12     <comments>Deleted</comments> 
 13    </currency>
 14    <currency>
 15     <currid>INR</currid>
 16     <dmltype>A</dmltype>
 17     <comments>Added</comments> 
 18    </currency>
 19    <currency>
 20     <currid>SGD</currid>
 21     <dmltype>A</dmltype>
 22     <comments>Added</comments> 
 23    </currency>
 24   </prodid>
 25  </branchid>
 26  </curr>') val
 27  from dual)
 28  select 
 29    extractvalue(val, '/curr/branchid/@value') brchid,
 30    extractvalue(val, '/curr/branchid/prodid/@value') prodid, 
 31    extractvalue(value(x), '/currency/currid') currid, 
 32    extractvalue(value(x), '/currency/dmltype') dmltype, 
 33    extractvalue(value(x), '/currency/comments') comments
 34  FROM data, 
 35       TABLE(xmlsequence(extract(val, '//currency'))) x
 36  /
BRCHID  PRODID  CURRID  DMLTYPE COMMENTS
------- ------- ------- ------- --------
123     1234    USD     A       Added
123     1234    EUR     D       Deleted
123     1234    INR     A       Added
123     1234    SGD     A       Added

4 rows selected.

Regards
Michel
Re: XML parsing in Oracle [message #572884 is a reply to message #572883] Tue, 18 December 2012 06:52 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Hi Michel, from next time i will format my code.
1) do i need to do this every time i execute the proc?
SQL> col brchid format a7
SQL> col prodid format a7
SQL> col currid format a7
SQL> col dmltype format a7
SQL> col comments format a8

2)with data as ( select XMLTYPE('<curr> ... So i need not to use cursor? and


i am confused.. can u give me the exact script that would be helpful for me!
thank you.....!

[Updated on: Tue, 18 December 2012 07:08]

Report message to a moderator

Re: XML parsing in Oracle [message #572886 is a reply to message #572884] Tue, 18 December 2012 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) This is just for SQL*Plus to format the columns, you don't need them in a procedure.
2) It depends on how you get the data but this is the principle, or if yo want to still use your xmldox variable then replace "data" in my "from" by your "xmtable...".

Quote:
can u give me the exact script that would be helpful for me!


Can't you do it with what I posted?
Just put my query in your cursor.

Regards
Michel
Re: XML parsing in Oracle [message #572888 is a reply to message #572886] Tue, 18 December 2012 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Example:
SQL> create or replace PROCEDURE xmltest IS 
  2  
  3  v_xml_doc xmltype;
  4  
  5  cursor c is
  6  select 
  7    extractvalue(value(y), '/curr/branchid/@value') brchid,
  8    extractvalue(value(y), '/curr/branchid/prodid/@value') prodid, 
  9    extractvalue(value(x), '/currency/currid') currid, 
 10    extractvalue(value(x), '/currency/dmltype') dmltype, 
 11    extractvalue(value(x), '/currency/comments') comments
 12  FROM TABLE (XMLSEQUENCE (v_xml_doc.extract ('/curr'))) y, 
 13       TABLE(xmlsequence(extract(value(y), '//currency'))) x;
 14  
 15  BEGIN 
 16  
 17    v_xml_doc :=XMLTYPE
 18  ('<curr>
 19  <branchid value = "123">
 20   <prodid value = "1234">
 21    <currency>
 22     <currid>USD</currid>
 23     <dmltype>A</dmltype>
 24     <comments>Added</comments>
 25    </currency>
 26    <currency>
 27     <currid>EUR</currid>
 28     <dmltype>D</dmltype>
 29     <comments>Deleted</comments> 
 30    </currency>
 31    <currency>
 32     <currid>INR</currid>
 33     <dmltype>A</dmltype>
 34     <comments>Added</comments> 
 35    </currency>
 36    <currency>
 37     <currid>SGD</currid>
 38     <dmltype>A</dmltype>
 39     <comments>Added</comments> 
 40    </currency>
 41   </prodid>
 42  </branchid>
 43  </curr>');
 44  
 45    FOR c_rec in c LOOP
 46      dbms_output.put_line (c_rec.brchid||','||c_rec.prodid||','||c_rec.currid||','||
 47                            c_rec.dmltype||','|| c_rec.comments);
 48   END LOOP;
 49  
 50  END xmltest;
 51  /

Procedure created.

SQL> exec  xmltest;
123,1234,USD,A,Added
123,1234,EUR,D,Deleted
123,1234,INR,A,Added
123,1234,SGD,A,Added

PL/SQL procedure successfully completed.

Regards
Michel
Re: XML parsing in Oracle [message #572890 is a reply to message #572886] Tue, 18 December 2012 07:31 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
ok thank you very much Michel.....Smile
Re: XML parsing in Oracle [message #572994 is a reply to message #572888] Wed, 19 December 2012 05:09 Go to previous message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Thank you Michel...Smile
Thanks for the help.... As ususal you rock!
Previous Topic: Extracting values from the XML string
Next Topic: Problem in changing Graph dynamically in IE8. However it's working fine in IE6
Goto Forum:
  


Current Time: Thu Mar 28 17:25:29 CDT 2024