Home » Developer & Programmer » JDeveloper, Java & XML » Reading the xml value (Oralce 10g)
| Reading the xml value [message #619000] |
Thu, 17 July 2014 04:37  |
 |
sss111ind
Messages: 636
Registered: April 2012
Location: India
|
Senior Member |
|
|
Hi All,
I have xml in below format, How can I extract all the values in single query.
<records>
<job>MANAGER</job>
<details>
<ename>JONES</ename>
<sal>2975</sal>
<ename>BLAKE</ename>
<sal>2850</sal>
<ename>CLARK</ename>
<sal>2450</sal>
</details>
</records>
SELECT extractvalue(value(a),'job') job
from TABLE (xmlsequence(extract(xmltype('<records><job>MANAGER</job><details><ename>JONES</ename><sal>2975</sal><ename>BLAKE</ename><sal>2850</sal><ename>CLARK</ename><sal>2450</sal></details></records>'), '/records/job'))) a;
SELECT extractvalue(value(a),'ename') ename
from TABLE (xmlsequence(extract(xmltype('<records><job>MANAGER</job><details><ename>JONES</ename><sal>2975</sal><ename>BLAKE</ename><sal>2850</sal><ename>CLARK</ename><sal>2450</sal></details></records>'), '/records/details/ename'))) a;
SELECT extractvalue(value(a),'sal') sal
from TABLE (xmlsequence(extract(xmltype('<records><job>MANAGER</job><details><ename>JONES</ename><sal>2975</sal><ename>BLAKE</ename><sal>2850</sal><ename>CLARK</ename><sal>2450</sal></details></records>'), '/records/details/sal'))) a;
Regards,
Nathan
|
|
|
|
| Re: Reading the xml value [message #619003 is a reply to message #619000] |
Thu, 17 July 2014 04:55  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
<records>
<job>MANAGER</job>
<details>
<ename>JONES</ename>
<sal>2975</sal>
<ename>BLAKE</ename>
<sal>2850</sal>
<ename>CLARK</ename>
<sal>2450</sal>
</details>
</records>
how you got the above code? I think use loop concept.
[Updated on: Thu, 17 July 2014 04:55] Report message to a moderator |
|
|
|
| Re: Reading the xml value [message #619006 is a reply to message #619000] |
Thu, 17 July 2014 05:11 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> with data as (select xmltype('
2 <records>
3 <job>MANAGER</job>
4 <details>
5 <ename>JONES</ename>
6 <sal>2975</sal>
7 <ename>BLAKE</ename>
8 <sal>2850</sal>
9 <ename>CLARK</ename>
10 <sal>2450</sal>
11 </details>
12 </records>') v from dual
13 )
14 select extractvalue(value(x),'/records/job') job,
15 y.ename,
16 z.sal
17 from data, table(xmlsequence(extract(v,'/records'))) x,
18 xmltable('//details/ename' PASSING v
19 COLUMNS POSITION FOR ORDINALITY,
20 ename VARCHAR2(20) PATH '//ename') y,
21 xmltable('//details/sal' PASSING v
22 COLUMNS POSITION FOR ORDINALITY,
23 sal NUMBER PATH '//sal') z
24 where y.position = z.position
25 /
JOB ENAME SAL
---------- ---------- ----------
MANAGER JONES 2975
MANAGER BLAKE 2850
MANAGER CLARK 2450
|
|
|
|
| Re: Reading the xml value [message #619007 is a reply to message #619003] |
Thu, 17 July 2014 05:13 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Thu, 17 July 2014 10:55
<records>
<job>MANAGER</job>
<details>
<ename>JONES</ename>
<sal>2975</sal>
<ename>BLAKE</ename>
<sal>2850</sal>
<ename>CLARK</ename>
<sal>2450</sal>
</details>
</records>
how you got the above code? I think use loop concept.
You have no idea what xml is do you?
|
|
|
|
|
|
| Re: Reading the xml value [message #619011 is a reply to message #619009] |
Thu, 17 July 2014 05:25 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can construct and deconstruct xml in a loop but that's almost always the hard way.
Oracle supplies a large set of xml functions for those jobs.
See Michel's solution above for an example.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Reading the xml value [message #619022 is a reply to message #619006] |
Thu, 17 July 2014 07:07 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 17 July 2014 15:41
SQL> with data as (select xmltype('
2 <records>
3 <job>MANAGER</job>
4 <details>
5 <ename>JONES</ename>
6 <sal>2975</sal>
7 <ename>BLAKE</ename>
8 <sal>2850</sal>
9 <ename>CLARK</ename>
10 <sal>2450</sal>
11 </details>
12 </records>') v from dual
13 )
14 select extractvalue(value(x),'/records/job') job,
15 y.ename,
16 z.sal
17 from data, table(xmlsequence(extract(v,'/records'))) x,
18 xmltable('//details/ename' PASSING v
19 COLUMNS POSITION FOR ORDINALITY,
20 ename VARCHAR2(20) PATH '//ename') y,
21 xmltable('//details/sal' PASSING v
22 COLUMNS POSITION FOR ORDINALITY,
23 sal NUMBER PATH '//sal') z
24 where y.position = z.position
25 /
JOB ENAME SAL
---------- ---------- ----------
MANAGER JONES 2975
MANAGER BLAKE 2850
MANAGER CLARK 2450
Michel, I am trying to understand why it doesn't give me the same output as yours :
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> column job format A15;
SQL>
SQL> with data as (select xmltype('
2 <records>
3 <job>MANAGER</job>
4 <details>
5 <ename>JONES</ename>
6 <sal>2975</sal>
7 <ename>BLAKE</ename>
8 <sal>2850</sal>
9 <ename>CLARK</ename>
10 <sal>2450</sal>
11 </details>
12 </records>') v from dual
13 )
14 select extractvalue(value(x),'/records/job') job,
15 y.ename,
16 z.sal
17 from data, table(xmlsequence(extract(v,'/records'))) x,
18 xmltable('//details/ename' PASSING v
19 COLUMNS POSITION FOR ORDINALITY,
20 ename VARCHAR2(20) PATH '//ename') y,
21 xmltable('//details/sal' PASSING v
22 COLUMNS POSITION FOR ORDINALITY,
23 sal NUMBER PATH '//sal') z
24 where y.position = z.position;
JOB ENAME SAL
--------------- -------------------- ----------
MANAGER
MANAGER
MANAGER
|
|
|
|
|
|
| Re: Reading the xml value [message #619029 is a reply to message #619027] |
Thu, 17 July 2014 08:10 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
1 with data as (select xmltype('
2 <records>
3 <job>MANAGER</job>
4 <details>
5 <ename>JONES</ename>
6 <sal>2975</sal>
7 <ename>BLAKE</ename>
8 <sal>2850</sal>
9 <ename>CLARK</ename>
10 <sal>2450</sal>
11 </details>
12 </records>') v from dual
13 )
14 select extractvalue(value(x),'/records/job') job,
15 y.ename,
16 z.sal
17 from data, table(xmlsequence(extract(v,'/records'))) x,
18 xmltable('//details/ename' PASSING v
19 COLUMNS POSITION FOR ORDINALITY,
20 ename VARCHAR2(20) PATH '//ename') y,
21 xmltable('//details/sal' PASSING v
22 COLUMNS POSITION FOR ORDINALITY,
23 sal NUMBER PATH '//sal') z
24* where y.position = z.position
SQL> /
JOB ENAME SAL
--------------- -------------------- ----------
MANAGER JONES 2975
MANAGER BLAKE 2850
MANAGER CLARK 2450
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
It works for me.
|
|
|
|
| Re: Reading the xml value [message #619031 is a reply to message #619029] |
Thu, 17 July 2014 08:26 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I have two databases, recently applied 11.2.0.4 patchset for testing, other is still in 11.2.0.2. Let me execute the same query in both versions :
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> column job format A15;
SQL>
SQL> with data as (select xmltype('
2 <records>
3 <job>MANAGER</job>
4 <details>
5 <ename>JONES</ename>
6 <sal>2975</sal>
7 <ename>BLAKE</ename>
8 <sal>2850</sal>
9 <ename>CLARK</ename>
10 <sal>2450</sal>
11 </details>
12 </records>') v from dual
13 )
14 select extractvalue(value(x),'/records/job') job,
15 y.ename,
16 z.sal
17 from data, table(xmlsequence(extract(v,'/records'))) x,
18 xmltable('//details/ename' PASSING v
19 COLUMNS POSITION FOR ORDINALITY,
20 ename VARCHAR2(20) PATH '//ename') y,
21 xmltable('//details/sal' PASSING v
22 COLUMNS POSITION FOR ORDINALITY,
23 sal NUMBER PATH '//sal') z
24 where y.position = z.position
25 /
JOB ENAME SAL
--------------- -------------------- ----------
MANAGER JONES 2975
MANAGER BLAKE 2850
MANAGER CLARK 2450
Now in 11.2.0.4 :
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> column job format A15;
SQL>
SQL> with data as (select xmltype('
2 <records>
3 <job>MANAGER</job>
4 <details>
5 <ename>JONES</ename>
6 <sal>2975</sal>
7 <ename>BLAKE</ename>
8 <sal>2850</sal>
9 <ename>CLARK</ename>
10 <sal>2450</sal>
11 </details>
12 </records>') v from dual
13 )
14 select extractvalue(value(x),'/records/job') job,
15 y.ename,
16 z.sal
17 from data, table(xmlsequence(extract(v,'/records'))) x,
18 xmltable('//details/ename' PASSING v
19 COLUMNS POSITION FOR ORDINALITY,
20 ename VARCHAR2(20) PATH '//ename') y,
21 xmltable('//details/sal' PASSING v
22 COLUMNS POSITION FOR ORDINALITY,
23 sal NUMBER PATH '//sal') z
24 where y.position = z.position
25 /
JOB ENAME SAL
--------------- -------------------- ----------
MANAGER
MANAGER
MANAGER
So it doesn't work in 11.2.0.4
Regards,
Lalit
|
|
|
|
|
|
|
|
| Re: Reading the xml value [message #619141 is a reply to message #619034] |
Fri, 18 July 2014 06:29 |
|
sss111ind
Messages: 636
Registered: April 2012
Location: India
|
Senior Member |
|
|
For fetching the nodes of different xml format we have to manually construct plsql or change xml format to suit the coding.
And I think there is no direct sql possible for all type of xml formats.Please suggest anything wrong if we do as follows or any better option is available.
declare
l_str varchar2(1000):=
'<emp_details>
<deptno>20</deptno>
<job_emp_det>
<job>CLERK</job>
<ename>SMITH</ename>
<ename>ADAMS</ename>
</job_emp_det>
<job_emp_det>
<job>MANAGER</job>
<ename>JONES</ename>
</job_emp_det>
<job_emp_det>
<job>ANALYST</job>
<ename>FORD</ename>
<ename>SCOTT</ename>
</job_emp_det>
</emp_details>';
l_deptno varchar2(100);
type rec is record(c1 varchar2(100),c2 varchar2(100),c3 varchar2(100));
type tab is table of rec index by binary_integer;
tab1 tab;
l_counter number:=1;
begin
SELECT extractvalue(value(k),'deptno/text()') deptno into l_deptno from table(xmlsequence(extract(xmltype(l_str),'/emp_details/deptno'))) k ;
for l in (select extract(value(k),'job_emp_det') job_det from table(xmlsequence(extract(xmltype(l_str),'/emp_details/job_emp_det'))) k )loop
for i in (select extractvalue(value(k),'job/text()') job from table(xmlsequence(extract(l.job_det,'job_emp_det/job'))) k )loop
-- dbms_output.put_line(i.job);
for j in (select extractvalue(value(k),'ename/text()') ename from table(xmlsequence(extract(l.job_det,'job_emp_det/ename'))) k )loop
-- dbms_output.put_line(j.ename);
tab1(l_counter).c1:=l_deptno;
tab1(l_counter).c2:=i.job;
tab1(l_counter).c3:=j.ename;
l_counter:=l_counter+1;
end loop;
end loop;
end loop;
for i in 1 .. tab1.count loop
dbms_output.put_line(tab1(i).c1||' '||tab1(i).c2||' '||tab1(i).c3);
end loop;
end;
|
|
|
|
| Re: Reading the xml value [message #619148 is a reply to message #619141] |
Fri, 18 July 2014 07:03 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
sss111ind wrote on Fri, 18 July 2014 07:29
And I think there is no direct sql possible for all type of xml formats.
First of all your XML is not well formed. Try adding another department to it. Anyway:
with t as (
select xmltype('<emp_details>
<deptno>20</deptno>
<job_emp_det>
<job>CLERK</job>
<ename>SMITH</ename>
<ename>ADAMS</ename>
</job_emp_det>
<job_emp_det>
<job>MANAGER</job>
<ename>JONES</ename>
</job_emp_det>
<job_emp_det>
<job>ANALYST</job>
<ename>FORD</ename>
<ename>SCOTT</ename>
</job_emp_det>
</emp_details>') xmldoc from dual
)
select deptno,
job,
ename
from t,
xmltable(
'/emp_details'
passing xmldoc
columns
deptno number path 'deptno',
job_emp_det xmltype path 'job_emp_det'
),
xmltable(
'/job_emp_det'
passing job_emp_det
columns
job varchar2(10) path 'job',
ename_list xmltype path 'ename'
),
xmltable(
'/ename'
passing ename_list
columns
ename varchar2(20) path '/ename'
)
/
DEPTNO JOB ENAME
------ -------------------- ------
20 CLERK SMITH
20 CLERK ADAMS
20 MANAGER JONES
20 ANALYST FORD
20 ANALYST SCOTT
SQL>
SY.
|
|
|
|
| Re: Reading the xml value [message #619149 is a reply to message #619148] |
Fri, 18 July 2014 07:39 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Something even more strange than the bug we I reported yesterday. This time SY's query works fine in 11.2.0.4, but it throws internal error in 11.2.0.2
11.2.0.2
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> with t as (
2 select xmltype('<emp_details>
3 <deptno>20</deptno>
4 <job_emp_det>
5 <job>CLERK</job>
6 <ename>SMITH</ename>
7 <ename>ADAMS</ename>
8 </job_emp_det>
9 <job_emp_det>
10 <job>MANAGER</job>
11 <ename>JONES</ename>
12 </job_emp_det>
13 <job_emp_det>
14 <job>ANALYST</job>
15 <ename>FORD</ename>
16 <ename>SCOTT</ename>
17 </job_emp_det>
18 </emp_details>') xmldoc from dual
19 )
20 select deptno,
21 job,
22 ename
23 from t,
24 xmltable(
25 '/emp_details'
26 passing xmldoc
27 columns
28 deptno number path 'deptno',
29 job_emp_det xmltype path 'job_emp_det'
30 ),
31 xmltable(
32 '/job_emp_det'
33 passing job_emp_det
34 columns
35 job varchar2(10) path 'job',
36 ename_list xmltype path 'ename'
37 ),
38 xmltable(
39 '/ename'
40 passing ename_list
41 columns
42 ename varchar2(20) path '/ename'
43 )
44 /
<ename>SMITH</ename>
*
ERROR at line 6:
ORA-00600: internal error code, arguments: [kkoljt1], [], [], [], [], [], [],
[], [], [], [], []
11.2.0.4
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> with t as (
2 select xmltype('<emp_details>
3 <deptno>20</deptno>
4 <job_emp_det>
5 <job>CLERK</job>
6 <ename>SMITH</ename>
7 <ename>ADAMS</ename>
8 </job_emp_det>
9 <job_emp_det>
10 <job>MANAGER</job>
11 <ename>JONES</ename>
12 </job_emp_det>
13 <job_emp_det>
14 <job>ANALYST</job>
15 <ename>FORD</ename>
16 <ename>SCOTT</ename>
17 </job_emp_det>
18 </emp_details>') xmldoc from dual
19 )
20 select deptno,
21 job,
22 ename
23 from t,
24 xmltable(
25 '/emp_details'
26 passing xmldoc
27 columns
28 deptno number path 'deptno',
29 job_emp_det xmltype path 'job_emp_det'
30 ),
31 xmltable(
32 '/job_emp_det'
33 passing job_emp_det
34 columns
35 job varchar2(10) path 'job',
36 ename_list xmltype path 'ename'
37 ),
38 xmltable(
39 '/ename'
40 passing ename_list
41 columns
42 ename varchar2(20) path '/ename'
43 )
44 /
DEPTNO JOB ENAME
---------- ---------- --------------------
20 CLERK SMITH
20 CLERK ADAMS
20 MANAGER JONES
20 ANALYST FORD
20 ANALYST SCOTT
So this time a bug in 11.2.0.2?
Regards,
Lalit
|
|
|
|
|
|
| Re: Reading the xml value [message #619170 is a reply to message #619168] |
Fri, 18 July 2014 09:42 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
I have asked my DBA to look into it and take action accordingly. If he raises a SR, I would keep you guys posted about what Oracle support replies.
|
|
|
|
| Re: Reading the xml value [message #621777 is a reply to message #619170] |
Mon, 18 August 2014 04:28 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
A quick update.
For the above mentioned bug, please see Bug 12381289 : SELECT WITH WITH CLAUSE(SUBFACTORING) AND XMLTYPE THROWS ORA-600 [KKOLJT1] created on 20-Apr-2011. The workaround is to use NO_XML_QUERY_REWRITE hint. I also executed the original query in 12.1.0.1 and it worked fine with no issues.
Regards,
Lalit
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:35:25 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
