Hi,
I have to extract value from multiple node from a xml file. I am using the below query , but it says "ORA-31013: Invalid XPATH expression"
select extractvalue(value(wl),'*/ns2:CaptureTypeCode','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as CaptureTypeCode,
extractvalue(value(wl),'*/ns2:TransactionCode','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"')as TransactionCode,
extractvalue(value(wl),'*/ns2:AttentionKey','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as AttentionKey,
extractvalue(value(wl),'*/ns2:ReferenceNumber','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as ReferenceNumber,
extractvalue(value(wl),'*/ns2:OrderNumber','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as OrderNumber,
extractvalue(value(wl),'*/ns2:FleetNumber','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as FleetNumber,
extractvalue(value(wl),'*/ns2:SubEntityNumber','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as SubEntityNumber,
extractvalue(value(wl),'*/ns2:TransactionDateTime','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as TransactionDateTime,
extractvalue(value(wl),'*/ns2:AuthorisationNumber','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as AuthorisationNumber ,
extractvalue(value(wl),'*/ns2:OdometerReading','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as OdometerReading,
extractvalue(value(wl),'*/ns2:BatchNumber','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as BatchNumber,
extractvalue(value(wl),'*/ns2:BatchSource','xmlns ns1="http://www.pp.com/ifcs" xmlns ns2="http://www.pp.com/core"') as BatchSource
from SAP_PRICE_FILE_TBL1 t, TABLE(xmlsequence(EXTRACT((t.content),'/ns1:LoadCardTransactions/ns1:LoadCardTransaction/ns1:Transaction'))) wl;
Please help.