How to retrieve different namespace values in one XML

Assumption:  <RStatus> node (if exists) is 2 levels up in the tree from <ID> node.

SQL> with t (xml) as (

select xmltype(

'
<q1:ARRow xmlns:q1="urn:org:abcd:message:ARR:v3.2.0">

<BEnv>

<BID>ASFRE1234567</BID>

<BDateTime>2018-05-04</BDateTime>

<DeliveryMethod>FLOW</DeliveryMethod>

<DestAgency><Organization>

<OrganizationName> xx college</OrganizationName>

</Organization></DestAgency></BEnv>

<BContent>

<q1:TResponse xmlns:q1="urn:org:abcd:message:TResponse:v1.4.0">

<q1:TData>

<CreatedDateTime>2018-05-04</CreatedDateTime>

<DocumentTypeCode>Res</DocumentTypeCode>

<Source><Organization><LocalOrganizationID />

<OrganizationName>OrgName</OrganizationName>

<Contacts><Email><EmailAddress>Email</EmailAddress></Email></Contacts></Organization></Source>

</q1:TData>

<q1:Response><CreatedDate>2018-05-04</CreatedDate>

<TrackingID>FRTG1987654</TrackingID>

<RStatus>NoRecord</RStatus><ReqS>

<Person><AssignedID>NA</AssignedID>

<ID>ST00001</ID>

<Birth><BirthDate>207-11-22</BirthDate></Birth><Name><FirstName>Belaine</FirstName>

<LastName>Leali</LastName></Name><HighSchool /></Person>

</ReqS>

<NMessage>No  record </NMessage>

</q1:Response>

</q1:TResponse>





<q1:HST xmlns:q1="urn:org:abcd:message:HST:v1.6.0"><TData>

<CreatedDate>2018-05-04</CreatedDate>

<DocumentTypeCode>ReqRecord</DocumentTypeCode></TData>

<Company><Person>

<AssignedID>NA</AssignedID>

<ID>ST00002</ID>

<Birth><BirthDate>1965-02-18</BirthDate></Birth>

<Name><FirstName>Johny</FirstName><MiddleName>Fore</MiddleName>

<LastName>Tramun</LastName></Name>

</Person>

</Company>

</q1:HST></BContent></q1:ARRow>

'
) from dual)
select z.* 
from t, xmltable(
  xmlnamespaces ('urn:org:abcd:message:ARR:v3.2.0' as "q1"),
  '//ID'
  passing t.xml
  columns
  id varchar2(30) path '.'
  ,rstatus varchar2(30) path './parent::*/parent::*/parent::*/RStatus'
) z

ID                             RSTATUS                      
------------------------------ ------------------------------
ST00001                        NoRecord                      
ST00002           

EDIT: corrected the hierarchy level.

select z.* 
from t, xmltable(
  xmlnamespaces ('urn:org:abcd:message:ARR:v3.2.0' as "q1"),
  '//ID'
  passing t.xml
  columns
  id varchar2(30) path '.'
  ,rstatus varchar2(30) path './../../../RStatus'
) z

Here a solution using alternative paths and all namespaces:

select id,rstatus
from t,
    xmltable(
        xmlnamespaces(
            'urn:org:abcd:message:ARR:v3.2.0'       as "q1",
            'urn:org:abcd:message:TResponse:v1.4.0' as "q2",
            'urn:org:abcd:message:HST:v1.6.0'       as "q3"),
        '/q1:ARRow/BContent/q2:TResponse/q2:Response|/q1:ARRow/BContent/q3:HST/Company'
        passing xml
        columns
            id         varchar2(10) path 'ReqS/Person/ID|Person/ID',
            rstatus    varchar2(20) path 'RStatus'       
    )
  

ID         RSTATUS             
---------- --------------------
ST00001    NoRecord            
ST00002                        

Hi mNem,

you can remove the namespace declaration in your solutions, since you are nowhere referring to q1 in your xpath expressions, it is not necessary.

Hi cormaco,

Thanks for correcting me. Appreciated.

Best regards,

mNem