Home » Developer & Programmer » JDeveloper, Java & XML » XML parsing in Oracle (Oracle 11g)
| XML parsing in Oracle [message #572870] |
Tue, 18 December 2012 06:04  |
 |
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  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
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 #572888 is a reply to message #572886] |
Tue, 18 December 2012 07:25 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:19:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
