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!

XMLTABLE - Optional Multiple Node

Manuel VidigalOct 27 2014 — edited Oct 28 2014

Hi all,

I'm having some trouble correcting the following XML query:

SELECT x.*

FROM xmltable(xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV",

'urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Root' AS "ns0",

'http://xmlns.tibcopsg.com/EAI/SharedResources' AS "ns1",

'urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Data' AS "ns2"),

'for $i in SOAP-ENV:Envelope/SOAP-ENV:Body/ns0:outputMessage,

$m in $i/ns1:Header/ns1:Status/ns1:eCodes,

$j in $i/ns1:Header/ns1:Status/ns1:eNative return <deNormalized>{$i}{$m}{$j}</deNormalized>'

passing xmltype('<?xml version="1.0" encoding="UTF-8"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

<SOAP-ENV:Body>

<ns0:outputMessage xmlns:ns0="urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Root">

<ns1:Header xmlns:ns1="http://xmlns.tibcopsg.com/EAI/SharedResources">

<ns1:Status>

<ns0:eCodes xmlns:ns0="http://xmlns.tibcopsg.com/EAI/SharedResources" xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">

<ns0:eCode>COM-998</ns0:eCode>

<ns0:eDescription>Error on Target system</ns0:eDescription>

</ns0:eCodes>

<ns0:eNative xmlns:ns0="http://xmlns.tibcopsg.com/EAI/SharedResources" xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">

<ns0:eCode>soapenv:Server.userException</ns0:eCode>

<ns0:eDescription>ERROR (326): Campo requerido não pode ficar em branco.; AST:AssetPeople : PeopleGroup Form Entry ID</ns0:eDescription>

</ns0:eNative>

<ns0:eNative xmlns:ns0="http://xmlns.tibcopsg.com/EAI/SharedResources" xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">

<ns0:eCode>000000000006709</ns0:eCode>

<ns0:eDescription />

</ns0:eNative>

</ns1:Status>

</ns1:Header>

<ns1:DataOutput xmlns:ns1="urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Data">

<ns1:TechnicalService ID="VF_IPCENTREX2" Notes="teste6" Type="IP Centrex">

<ns1:Clients ClientID="12903036" ClientName="JAPAUTOMOTIVE - COMÉRCIO DE AUTOMOVEIS, S.A." ClientOwner="S" CustomerGroup="Corporate" ID="VF_IPCENTREX2" NIF="505897210" />

</ns1:TechnicalService>

</ns1:DataOutput>

</ns0:outputMessage>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>')

columns ecode VARCHAR2(2000) path 'ns1:eCodes/ns1:eCode',

edescription VARCHAR2(2000) path 'ns1:eCodes/ns1:eDescription',

target_sys_code VARCHAR2(2000) path 'ns1:eNative/ns1:eCode',

target_sys_desc VARCHAR2(2000) path 'ns1:eNative/ns1:eDescription',

ts_id VARCHAR2(2000) path 'ns0:outputMessage/ns2:DataOutput/ns2:TechnicalService/@ID') x;

It works fine when the multiple node "ns0:eNative" exists, but this multiple node is optional, and when it doesn't exist the query doesn't return rows:

SELECT x.*

FROM xmltable(xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV",

'urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Root' AS "ns0",

'http://xmlns.tibcopsg.com/EAI/SharedResources' AS "ns1",

'urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Data' AS "ns2"),

'for $i in SOAP-ENV:Envelope/SOAP-ENV:Body/ns0:outputMessage,

$m in $i/ns1:Header/ns1:Status/ns1:eCodes,

$j in $i/ns1:Header/ns1:Status/ns1:eNative return <deNormalized>{$i}{$m}{$j}</deNormalized>'

passing xmltype('<?xml version="1.0" encoding="UTF-8"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

<SOAP-ENV:Body>

<ns0:outputMessage xmlns:ns0="urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Root">

<ns1:Header xmlns:ns1="http://xmlns.tibcopsg.com/EAI/SharedResources">

<ns1:Status>

<ns0:eCodes xmlns:ns0="http://xmlns.tibcopsg.com/EAI/SharedResources" xmlns:jms1="http://www.tibco.com/namespaces/tnt/plugins/jms">

<ns0:eCode>COM-997</ns0:eCode>

<ns0:eDescription>Functional Error</ns0:eDescription>

</ns0:eCodes>

</ns1:Status>

</ns1:Header>

<ns1:DataOutput xmlns:ns1="urn://SONAECOM/TIBCO/CORP/Services/GetTechnicalServiceInfo/Data" />

</ns0:outputMessage>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>')

columns ecode VARCHAR2(2000) path 'ns1:eCodes/ns1:eCode',

edescription VARCHAR2(2000) path 'ns1:eCodes/ns1:eDescription',

target_sys_code VARCHAR2(2000) path 'ns1:eNative/ns1:eCode',

target_sys_desc VARCHAR2(2000) path 'ns1:eNative/ns1:eDescription',

ts_id VARCHAR2(2000) path 'ns0:outputMessage/ns2:DataOutput/ns2:TechnicalService/@ID') x;

What is the best way to fix this?

Thanks in advance.

This post has been answered by odie_63 on Oct 28 2014

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 25 2014

Added on Oct 27 2014

6 comments

579 views