Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to extract a particular value from an XML column?

User_83ESOJun 1 2021

Hello
The gv$sql_monitor view contains a column named bind_xml which, for example, has the following content

<binds>
  <bind name=":B2" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="6">1714200338</bind>
  <bind name=":B1" pos="2" dty="2" dtystr="NUMBER" maxlen="22" len="2">47</bind>
 </binds>

I want to get the value of bind variable :B1: for example in this case : 47
how to use the extract value in this case

 SELECT 
   sql_exec_id
	,to_char(sql_exec_start, 'dd/mm/yyyy hh24:mi:ss') exec_time
	,trunc(elapsed_time/1e6) elap
	,status
	,EXTRACTVALUE(XMLType(binds_xml),'/binds/bind name') bind_val	
FROM 
  gv$sql_monitor 
where sql_id = '1wmh5fj8zmuzp';

Thanks

This post has been answered by Jason_(A_Non) on Jun 1 2021
Jump to Answer
Comments
Post Details
Added on Jun 1 2021
3 comments
1,013 views