0

I need to get the value of the tag "supervisor....." The problem is that those tag name are dynamic, they change for each person. I have tried using Extract value but it works only if you use a static tag name.

It is in Oracle environment.

Thanks for your help.

Ourson

XML Extract
    <?xml version="1.0" encoding="UTF-8"?>
    <TRANSACTION>
       <TransCtx>
          <supervisorCalcAttributes classType="Ht">
             <SupervisorId10977 classType="s">Matt, Clinton</SupervisorId10977>
             <SupervisorId4753 classType="s">Bob, Sponge</SupervisorId4753>
             <FND_ENTERPRISE_ID classType="s">1</FND_ENTERPRISE_ID>
          </supervisorCalcAttributes>
       </TransCtx>
    </TRANSACTION>

SQL Query

select
Extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),
'root/TRANSACTION[1]/TransCtx[1]/supervisorCalcAttributes[1]/**LineItem**[1]' ) as test
from hrc_txn_data txndata, hrc_txn_header txnhe 
where txnhe.transaction_id=txndata.transaction_id
user3640575
  • 1
  • 1
  • Who did that? Should be – Tony Hopkinson May 15 '14 at 11:55
  • It is a big system.... so impossible to modify the xml. I must find a solution to be able to get the value... their is always just 2 values and the id change for every person impossible to know the ID ;( – user3640575 May 15 '14 at 11:58
  • possible duplicate of [XPath wildcards on node name](http://stackoverflow.com/questions/4203119/xpath-wildcards-on-node-name) – Tony Hopkinson May 15 '14 at 12:00
  • I have tried but it is not working....select Extractvalue( xmltype(''||txndata.data_cache||''), 'root/TRANSACTION[1]/TransCtx[1]/supervisorCalcAttributes[1]/*[substring(name(), string-length(name()) 10) = 'Supervisor']') – user3640575 May 15 '14 at 12:24
  • Extractvalue( xmltype(''||txndata.data_cache||''), 'root/TRANSACTION[1]/TransCtx[1]/supervisorCalcAttributes[1]/Supervisor*[1]' ) as test – user3640575 May 15 '14 at 12:25
  • Result java.sql.SQLException: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00601: Invalid token in: 'root/TRANSACTION[1]/TransCtx[1]/supervisorCalcAttributes[1]/Supervisor*' – user3640575 May 15 '14 at 12:30
  • I know XPath mate, don't know Oracle though, so not going to be much help to you if Oracle function won't have it. May be close this question, try dba.stackexchange with something more specific. – Tony Hopkinson May 15 '14 at 14:14

0 Answers0