extractvalue for xmltype [message #362710] |
Wed, 03 December 2008 18:37 |
casttree
Messages: 83 Registered: August 2008
|
Member |
|
|
I hope to get the value of ZZZZZZ from the following xml in sql. If I use extractValue or extract of xmlType, what tag should I retrieve the value? looks I tried "info" , and it is not working. or maybe my syntax is wrong?
<report>
<task_stats>
<info_group name="task_info">
<info name="status">ZZZZZZZ</info>
</info_group>
</task_stats>
</report>
[Updated on: Thu, 04 December 2008 00:15] by Moderator Report message to a moderator
|
|
|
|
Re: extractvalue for xmltype [message #362970 is a reply to message #362724] |
Thu, 04 December 2008 13:10 |
casttree
Messages: 83 Registered: August 2008
|
Member |
|
|
I use the following code and the sqlplus get the error pls-00801
DECLARE
key_enable varchar2(20);
rept varchar2(4000)
......
-- rept get xml data from other process
-- rept= dbms_report.get_report(pre_rept);
-- I tried extract and extractValue as below
BEGIN
select XMLType(rept).extractValue('/info') into key_enable from dual;
select XMLType(rept).extract('/info') into key_enable from dual;
END
*
the sqlplus run the script and get the following error messges
ERROR at line 19:
ORA-06550: line 19, column 3:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 812; Cannot coerce
between type 49 and type 31; Xanon__0x2e7b0660__AB[19, 3]]
Thanks,
[Updated on: Thu, 04 December 2008 13:16] Report message to a moderator
|
|
|
|
Re: extractvalue for xmltype [message #362977 is a reply to message #362973] |
Thu, 04 December 2008 13:26 |
casttree
Messages: 83 Registered: August 2008
|
Member |
|
|
I don't have the source code for the pre_rept, but I printed out the rept , the rept as below:
dbms_output.put_line('rept is ' || rept);
<report>
<task_stats>
<info_group name="task_info">
<info name="status">ZZZZZZZ</info>
</info_group>
</task_stats>
</report>
|
|
|
|
Re: extractvalue for xmltype [message #363004 is a reply to message #362710] |
Thu, 04 December 2008 15:02 |
casttree
Messages: 83 Registered: August 2008
|
Member |
|
|
Thanks!
anything diffeence between sql and pl/sql I need to change? tried the following and get some errors.
DECLARE
rept varchar2(4000);
rept_xml varchar2(4000);
rref varchar2(4000);
key_enable varchar2(20);
BEGIN
---other prceure to get rref rref := dbms_report.build_report_reference_varg('xxxxx') ;
rept := dbms_report.get_report(rref);
dbms_output.put_line('rept is ' || rept);
-- rept output as above sample
with data as ( select xmltype(rept) rept_xml from dual)
-- select extractvalue(rept_xml, '/report/task_stats/info_group/info') val from dual;
select extractvalue(rept_xml, '/report/task_stats/info_group/info') into key_enable from dual;
select extractvalue(rept_xml, '/report/task_stats/info_group/info').getstringval() into key_enable from dual;
dbms_output.put_line( 'key_enable is ' || key_enable);
END
SQL> @key2
select extractvalue(rept_xml, '/report/task_stats/info_group/info') into key_enable from dual;
*
ERROR at line 19:
ORA-06550: line 19, column 10:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 17, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 20, column 10:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 20, column 3:
PL/SQL: SQL Statement ignored
Elapsed: 00:00:00.01
|
|
|
|
Re: extractvalue for xmltype [message #363123 is a reply to message #363004] |
Fri, 05 December 2008 02:51 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Clean your test case, it is so a mess that you can't see the obvious. For instance:
SQL> DECLARE
2 rept varchar2(4000);
3 rept_xml varchar2(4000);
4 rref varchar2(4000);
5 key_enable varchar2(20);
6
7 BEGIN
8
9 -- rept := dbms_report.get_report(rref);
10 rept := '<report>
11
12 <task_stats>
13 <info_group name="task_info">
14 <info name="status">ZZZZZZZ</info>
15 </info_group>
16 </task_stats>
17
18 </report>';
19 dbms_output.put_line('rept is ' || rept);
20 -- rept output as above sample
21
22 with data as ( select xmltype(rept) rept_xml from dual)
23 select extractvalue(rept_xml, '/report/task_stats/info_group/info')
24 into key_enable from data; -- not dual
25
26 dbms_output.put_line( 'key_enable is ' || key_enable);
27
28 END;
29 /
rept is <report>
<task_stats>
<info_group name="task_info">
<info name="status">ZZZZZZZ</info>
</info_group>
</task_stats>
</report>
key_enable is ZZZZZZZ
PL/SQL procedure successfully completed.
Regards
Michel
[Updated on: Fri, 05 December 2008 02:51] Report message to a moderator
|
|
|
|
Re: extractvalue for xmltype [message #385728 is a reply to message #362988] |
Tue, 10 February 2009 18:01 |
casttree
Messages: 83 Registered: August 2008
|
Member |
|
|
Can we specify the sub attributes "name="status" when there are multiple attrubutes ?
<task_stats>
<info_group name="task_info">
<info name="status_1">ZZZZZZZ</info>
<info name="status_2">xxxxxx</info>
</info_group>
</task_stats>
select extractvalue(rept_xml, '/report/task_stats/info_group/info[position()=1]')
into key_enable from data;
I can use the extractvalue with position parameter to get the first element of info, but thought we may specify the parameter (like "name+"status_1" ) to limit the query, but don't find how to do it.
Is there a way that we can specify the paramter for this?
Thanks,
|
|
|