Oracle XML解析值为null

你好，
我正在尝试使用附加查询解析INQORDERSTAT_SYS的值，但返回null。
结果应该有下面这样的两行。你能指教吗？

INQORDERSTAT_SYS
----------------
AADS
奥巴

with x as (
select xmltype('

   
      
         
         00000446
         0
         
         奥巴20
         SYNC
         
            STATUSPR
            
            
            
            341
         
      
      
         0
         
         3988600
         1
         2
         2
         
            0
            1
            ANI DEAC
            08172009
            PROCES
            08172009
         
         
            AADS
          
         
            奥巴
            
         
   
') 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'      
 );

您需要在路径中包含名称空间:

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

这给出了:

with x as (
select xmltype('

   
      
         
         00000446
         0
         
         OBUS20
         SYNC
         
            STATUSPR
            
            
            
            341
         
      
      
         0
         
         3988600
         1
         2
         2
         
            0
            1
            ANI DEAC
            08172009
            PROCES
            08172009
         
         
            AADS
          
         
            OBUS
            
         
   
') 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