Skip to Main Content
Forums

DevOps, CI/CD and Automation

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!

How to retrieve different namespace values in one XML

User_S9M3BJun 6 2018 — edited Nov 5 2018

Hi  ,

I have two different namespaces in one xml . Here ID tag which is common to both namespaces .

ID                      RStatus

------------------------------------------

ST00001           No Record

ST00002           blank

XML looks like : --

<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>
This post has been answered by mNem on Jun 6 2018
Jump to Answer

Comments

mNem

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.

mNem
Answer 
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
Marked as Answer by User_S9M3B · Sep 27 2020
cormaco

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                       
1 person found this helpful
cormaco

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.

mNem

Hi cormaco,

Thanks for correcting me. Appreciated.

Best regards,

mNem

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 4 2018
Added on Jun 6 2018
#pl-sql-xml-programming, #xml
5 comments
694 views