Home » Developer & Programmer » JDeveloper, Java & XML » extractvalue for xmltype
extractvalue for xmltype [message #362710] Wed, 03 December 2008 18:37 Go to next message
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 #362724 is a reply to message #362710] Thu, 04 December 2008 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
looks I tried "info" , and it is not working. or maybe my syntax is wrong?

Maybe if you post it we can say.

Use code tags not quote one to post code.

Regards
Michel
Re: extractvalue for xmltype [message #362970 is a reply to message #362724] Thu, 04 December 2008 13:10 Go to previous messageGo to next message
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 #362973 is a reply to message #362970] Thu, 04 December 2008 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you don't post the whole and actual code we can't help.

Regards
Michel
Re: extractvalue for xmltype [message #362977 is a reply to message #362973] Thu, 04 December 2008 13:26 Go to previous messageGo to next message
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 #362988 is a reply to message #362977] Thu, 04 December 2008 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So you have rep, you don't need the code of the procedure that generates it to post an execution of what you have after:
SQL> with data as (
  2    select xmltype('<report>
  3  <task_stats>
  4  <info_group name="task_info">
  5  <info name="status">ZZZZZZZ</info>
  6  </info_group>
  7  </task_stats>
  8  </report>') d from dual
  9    )
 10  select extractvalue(d,'/report/task_stats/info_group/info') val
 11  from data
 12  /
VAL
----------------------------------------------------------------
ZZZZZZZ

1 row selected.

Regards
Michel
Re: extractvalue for xmltype [message #363004 is a reply to message #362710] Thu, 04 December 2008 15:02 Go to previous messageGo to next message
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 #363040 is a reply to message #363004] Thu, 04 December 2008 22:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Which line is line 19?
Use SQL*Plus and copy and paste your session as I did (with "set echo on" if you call a script).
Post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 04 December 2008 22:59]

Report message to a moderator

Re: extractvalue for xmltype [message #363123 is a reply to message #363004] Fri, 05 December 2008 02:51 Go to previous messageGo to next message
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 #363343 is a reply to message #363123] Sat, 06 December 2008 00:00 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
Thanks! it works now.
Re: extractvalue for xmltype [message #385728 is a reply to message #362988] Tue, 10 February 2009 18:01 Go to previous message
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,





Previous Topic: Problem with section breaks in XML Publisher template
Next Topic: Binding variable for DBMS_SPM.NAME_LIST in OracleCallableStatement
Goto Forum:
  


Current Time: Tue Dec 17 22:42:12 CST 2024