Home » Developer & Programmer » JDeveloper, Java & XML » Parsing XML in Oracle 9i
Parsing XML in Oracle 9i [message #272205] Thu, 04 October 2007 04:05 Go to next message
sghoshal
Messages: 5
Registered: April 2007
Location: Kolkata
Junior Member
Hi,

I have the following XML in the table po_xml_tab.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://capabilities.nat.bt.com/xsd/2007/04/20" xmlns:stan="http://wsi.nat.bt.com/2005/06/StandardHeader/" xmlns:ser="http://capabilities.nat.bt.com/xsd/Services" xmlns:res="http://capabilities.nat.bt.com/xsd/Resources" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sup="http://capabilities.nat.bt.com/xsd/Parties/PartyRoles/Supplier" xmlns:agr="http://capabilities.nat.bt.com/xsd/Agreements" xmlns:cus="http://capabilities.nat.bt.com/xsd/Customer">
<soapenv:Body>
<ns:serviceNotification1>
<stan:standardHeader>
<stan:e2e>
<stan:E2EDATA>E2E Logging Information</stan:E2EDATA>
</stan:e2e>
<stan:serviceState>
<stan:stateCode>OK</stan:stateCode>
<stan:errorCode>0</stan:errorCode>
<stan:errorDesc>Success</stan:errorDesc>
<stan:errorText>Success</stan:errorText>
</stan:serviceState>
</stan:standardHeader>
</ns:serviceNotification1>
</soapenv:Body>
</soapenv:Envelope>


I want to select the value within the tags <stan:stateCode> and </stan:stateCode> ie I want to read the value "OK".

I am using the following query but am getting an error message ORA Error "Invalid XPATH Expression".

Can anybody please help.

This is the query that I am using.

select a, extract
(e.b_data,'/soapenv:Envelope/soapenv:Body/ns:serviceNotification1/stan:standardHeader/stan:serviceState/stan:stateCode/text()').getst ringVal() datas
from PO_XML_TAB e
Re: Parsing XML in Oracle 9i [message #272244 is a reply to message #272205] Thu, 04 October 2007 05:26 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
When the XML has namespaces, the EXTRACT function needs a third argument. This should work:

SELECT EXTRACT(e.b_data, 
  '/soapenv:Envelope/soapenv:Body/ns:serviceNotification1/stan:standardHeader/stan:serviceState/stan:stateCode/text()',
   'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:ns="http://capabilities.nat.bt.com/xsd/2007/04/20" 
   xmlns:stan="http://wsi.nat.bt.com/2005/06/StandardHeader/"').getStringVal() datas
FROM po_xml_tab e
Previous Topic: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
Next Topic: PL/SQL Cookie management when consuming external web service
Goto Forum:
  


Current Time: Fri Dec 09 13:47:05 CST 2016

Total time taken to generate the page: 0.10413 seconds