Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Narendran.

Asked: November 27, 2019 - 5:27 pm UTC

Last updated: December 05, 2019 - 9:23 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hello,
I'm trying to parse the value of INQORDERSTAT_SYS using the attached query but its returning null.
The result should have two rows like below.Can you please advise?

INQORDERSTAT_SYS
----------------
AADS
OBUS


with x as (
select xmltype('<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <inqorderstatus:msg_INQORDERSTATRESPONSE xmlns:inqorderstatus="http://www.abc.com/InqOrderStatus" xmlns:abc="http://www.abc.com/oagis/9" xmlns:abcws="http://www.abc.com/ASBO">
      <NS1:OGCM_PPG_HEADER xmlns:NS1="http://www.abc.com/InqOrderStatus">
         <NS1:OGCM_PPG_DIVISIONS />
         <NS1:OGCM_PPG_MESSAGE_ID>00000446</NS1:OGCM_PPG_MESSAGE_ID>
         <NS1:OGCM_PPG_RESP_CODE>0</NS1:OGCM_PPG_RESP_CODE>
         <NS1:OGCM_PPG_ERROR_CODE />
         <NS1:OGCM_PPG_DEST_ID>OBUS20</NS1:OGCM_PPG_DEST_ID>
         <NS1:OGCM_PPG_TRANSPORT_TYPE>SYNC</NS1:OGCM_PPG_TRANSPORT_TYPE>
         <NS1:OGCM_PPG_UNIQUE_RED>
            <NS1:PPG_ORIG_ID>STATUSPR</NS1:PPG_ORIG_ID>
            <NS1:PPG_UNIQUE_ID />
            <NS1:OGCM_PPG_EXT_AUDIT_SW />
            <NS1:fill_0 />
            <NS1:OGCM_PPG_EXT_MSG_LEN>341</NS1:OGCM_PPG_EXT_MSG_LEN>
         </NS1:OGCM_PPG_UNIQUE_RED>
      </NS1:OGCM_PPG_HEADER>
      <NS2:INQORDERSTAT_RESPONSE_MESSAGE xmlns:NS2="http://www.abc.com/InqOrderStatus">
         <NS2:INQORDERSTAT_RESP_CODE>0</NS2:INQORDERSTAT_RESP_CODE>
         <NS2:INQORDERSTAT_ERR_CODE />
         <NS2:INQORDERSTAT_TIN>3988600</NS2:INQORDERSTAT_TIN>
         <NS2:INQORDERSTAT_TIN_CTR>1</NS2:INQORDERSTAT_TIN_CTR>
         <NS2:INQORDERSTAT_PVSYS_CTR>2</NS2:INQORDERSTAT_PVSYS_CTR>
         <NS2:INQORDERSTAT_FAC_CTR>2</NS2:INQORDERSTAT_FAC_CTR>
         <NS2:INQORDERSTAT_TIN_INFO>
            <NS2:INQORDERSTAT_TIN_VER>0</NS2:INQORDERSTAT_TIN_VER>
            <NS2:INQORDERSTAT_TIN_PRDGRP>1</NS2:INQORDERSTAT_TIN_PRDGRP>
            <NS2:INQORDERSTAT_TIN_REQACT>ANI DEAC</NS2:INQORDERSTAT_TIN_REQACT>
            <NS2:INQORDERSTAT_TIN_RCVDT>08172009</NS2:INQORDERSTAT_TIN_RCVDT>
            <NS2:INQORDERSTAT_TIN_STATUS>PROCES</NS2:INQORDERSTAT_TIN_STATUS>
            <NS2:INQORDERSTAT_TIN_CHGDT>08172009</NS2:INQORDERSTAT_TIN_CHGDT>
         </NS2:INQORDERSTAT_TIN_INFO>
         <NS2:INQORDERSTAT_PVSYS_INFO>
            <NS2:INQORDERSTAT_PVSYS>AADS</NS2:INQORDERSTAT_PVSYS>
          </NS2:INQORDERSTAT_PVSYS_INFO>
         <NS2:INQORDERSTAT_PVSYS_INFO>
            <NS2:INQORDERSTAT_PVSYS>OBUS</NS2:INQORDERSTAT_PVSYS>
            </NS2:INQORDERSTAT_PVSYS_INFO>
         </NS2:INQORDERSTAT_RESPONSE_MESSAGE>
   </inqorderstatus:msg_INQORDERSTATRESPONSE>
</soapenv:Body>') as doc
from dual
)
 select INQORDERSTAT_SYS
 from x x, xmltable ( 
   xmlnamespaces (
     'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
     'http://www.abc.com/InqOrderStatus' as "inqorderstatus",
     'http://www.abc.com/oagis/9' as "abc",
     'http://www.abc.com/ASBO' as "abcws",
    'http://www.abc.com/InqOrderStatus' as "NS1"
   ),
   '/Body/msg_INQORDERSTATRESPONSE/INQORDERSTAT_RESPONSE_MESSAGE/INQORDERSTAT_PVSYS_INFO'
  --'/Body/inqorderstatus:msg_INQORDERSTATRESPONSE/NS1:OGCM_PPG_HEADER'
   passing x.doc 
   columns 
     INQORDERSTAT_SYS varchar2(10) path 'INQORDERSTAT_PVSYS'
   --  OGCM_PPG_MESSAGE_ID varchar2(10) path 'NS1:OGCM_PPG_MESSAGE_ID'      
 );


and Chris said...

You need to include the namespaces in your paths:

'/soapenv:Body/inqorderstatus:msg_INQORDERSTATRESPONSE/NS2:INQORDERSTAT_RESPONSE_MESSAGE/NS2:INQORDERSTAT_PVSYS_INFO'


Which gives:

with x as (
select xmltype('<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <inqorderstatus:msg_INQORDERSTATRESPONSE xmlns:inqorderstatus="http://www.abc.com/InqOrderStatus" xmlns:abc="http://www.abc.com/oagis/9" xmlns:abcws="http://www.abc.com/ASBO">
      <NS1:OGCM_PPG_HEADER xmlns:NS1="http://www.abc.com/InqOrderStatus">
         <NS1:OGCM_PPG_DIVISIONS />
         <NS1:OGCM_PPG_MESSAGE_ID>00000446</NS1:OGCM_PPG_MESSAGE_ID>
         <NS1:OGCM_PPG_RESP_CODE>0</NS1:OGCM_PPG_RESP_CODE>
         <NS1:OGCM_PPG_ERROR_CODE />
         <NS1:OGCM_PPG_DEST_ID>OBUS20</NS1:OGCM_PPG_DEST_ID>
         <NS1:OGCM_PPG_TRANSPORT_TYPE>SYNC</NS1:OGCM_PPG_TRANSPORT_TYPE>
         <NS1:OGCM_PPG_UNIQUE_RED>
            <NS1:PPG_ORIG_ID>STATUSPR</NS1:PPG_ORIG_ID>
            <NS1:PPG_UNIQUE_ID />
            <NS1:OGCM_PPG_EXT_AUDIT_SW />
            <NS1:fill_0 />
            <NS1:OGCM_PPG_EXT_MSG_LEN>341</NS1:OGCM_PPG_EXT_MSG_LEN>
         </NS1:OGCM_PPG_UNIQUE_RED>
      </NS1:OGCM_PPG_HEADER>
      <NS2:INQORDERSTAT_RESPONSE_MESSAGE xmlns:NS2="http://www.abc.com/InqOrderStatus">
         <NS2:INQORDERSTAT_RESP_CODE>0</NS2:INQORDERSTAT_RESP_CODE>
         <NS2:INQORDERSTAT_ERR_CODE />
         <NS2:INQORDERSTAT_TIN>3988600</NS2:INQORDERSTAT_TIN>
         <NS2:INQORDERSTAT_TIN_CTR>1</NS2:INQORDERSTAT_TIN_CTR>
         <NS2:INQORDERSTAT_PVSYS_CTR>2</NS2:INQORDERSTAT_PVSYS_CTR>
         <NS2:INQORDERSTAT_FAC_CTR>2</NS2:INQORDERSTAT_FAC_CTR>
         <NS2:INQORDERSTAT_TIN_INFO>
            <NS2:INQORDERSTAT_TIN_VER>0</NS2:INQORDERSTAT_TIN_VER>
            <NS2:INQORDERSTAT_TIN_PRDGRP>1</NS2:INQORDERSTAT_TIN_PRDGRP>
            <NS2:INQORDERSTAT_TIN_REQACT>ANI DEAC</NS2:INQORDERSTAT_TIN_REQACT>
            <NS2:INQORDERSTAT_TIN_RCVDT>08172009</NS2:INQORDERSTAT_TIN_RCVDT>
            <NS2:INQORDERSTAT_TIN_STATUS>PROCES</NS2:INQORDERSTAT_TIN_STATUS>
            <NS2:INQORDERSTAT_TIN_CHGDT>08172009</NS2:INQORDERSTAT_TIN_CHGDT>
         </NS2:INQORDERSTAT_TIN_INFO>
         <NS2:INQORDERSTAT_PVSYS_INFO>
            <NS2:INQORDERSTAT_PVSYS>AADS</NS2:INQORDERSTAT_PVSYS>
          </NS2:INQORDERSTAT_PVSYS_INFO>
         <NS2:INQORDERSTAT_PVSYS_INFO>
            <NS2:INQORDERSTAT_PVSYS>OBUS</NS2:INQORDERSTAT_PVSYS>
            </NS2:INQORDERSTAT_PVSYS_INFO>
         </NS2:INQORDERSTAT_RESPONSE_MESSAGE>
   </inqorderstatus:msg_INQORDERSTATRESPONSE>
</soapenv:Body>') as doc
from dual
)
 select INQORDERSTAT_SYS
 from x x, xmltable ( 
   xmlnamespaces (
     'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
     'http://www.abc.com/InqOrderStatus' as "inqorderstatus",
     'http://www.abc.com/oagis/9' as "abc",
     'http://www.abc.com/ASBO' as "abcws",
     'http://www.abc.com/InqOrderStatus' as "NS1",
     'http://www.abc.com/InqOrderStatus' as "NS2"
   ),
   '/soapenv:Body/inqorderstatus:msg_INQORDERSTATRESPONSE/NS2:INQORDERSTAT_RESPONSE_MESSAGE/NS2:INQORDERSTAT_PVSYS_INFO'
   passing x.doc 
   columns 
     INQORDERSTAT_SYS varchar2(10) path 'NS2:INQORDERSTAT_PVSYS'
 );

INQORDERSTAT_SYS   
AADS                
OBUS

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks it worked

Naren, December 02, 2019 - 5:16 pm UTC

You guys are amazing.I was struggling with this for a couple of days and this greatly helped:)
Chris Saxon
December 05, 2019 - 9:23 am UTC

:)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.