OraFAQ Forum: JDeveloper, Java & XML » Parsing XML in Oracle 9i

Home » Developer & Programmer » JDeveloper, Java & XML » Parsing XML in Oracle 9i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parsing XML in Oracle 9i [message #272205]

Thu, 04 October 2007 04:05

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

Report message to a moderator

Re: Parsing XML in Oracle 9i [message #272244 is a reply to message #272205]

Thu, 04 October 2007 05:26

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 14 12:53:18 CDT 2025