5 Replies Latest reply on Jun 6, 2018 5:11 PM by mNem

    How to retrieve different namespace values in one XML

    863516

      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>

        • 1. Re: How to retrieve different namespace values in one XML
          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.

          • 2. Re: How to retrieve different namespace values in one XML
            mNem
            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
            
            • 3. Re: How to retrieve different namespace values in one XML
              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                        
              
              
              
              • 4. Re: How to retrieve different namespace values in one XML
                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.

                • 5. Re: How to retrieve different namespace values in one XML
                  mNem

                  Hi cormaco,

                   

                  Thanks for correcting me. Appreciated.

                   

                  Best regards,

                  mNem