4 Replies Latest reply on Dec 9, 2019 8:11 AM by 2799196

    multiple lines with same xml tags

    2799196

      Hi

       

      <?xml version='1.0' encoding='UTF-8'?>
      <S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
      <S:Body><ns2:xyzResponse xmlns:ns2="http://xyz.xyz">
      <return>
          <resCode>0</resCode>
          <resDef>SUCCESS</resDef>
          <RecordResult>
              <no>1</no>
              <resCode>0</resCode>
              <resDef>SUCCESS</resDef>
              <refNo>191206011906</refNo>
              <no>2</no>
              <resCode>-1</resCode>
              <resDef>FAILURE</resDef>
              <refNo>191206011907</refNo>
          </RecordResult>
      </return>
      </ns2:xyzResponse>
      </S:Body>
      </S:Envelope>
      

       

      I have above XML and I 've been desperately trying to have it in columns.

       

      Not sure if I can create something like LOOP for xml paths.. But I want two different output:

      1-Server Response

      resCoderesDef
      0

      SUCCESS

       

      and

      2-Process Result

      noresCoderesDefrefNo
      00SUCCESS191206011906
      1-1FAILURE191206011907

       

      xmltable('//return'
              passing xmltype(x.service_response)
              columns
                  rescode varchar2(5) path '//resCode',
                  resDef varchar2(2000) path '//resDef'
      

       

      won't work. however, if I use resCode[1] it works. But only for the one line..

      I think it 's not possible to bind a variable to this node (or elements?) in plsql. I 've also tried to pass variables but couldn't achived any thing useful yet.

      Could you help me creating these queries?

       

      Thank you very much in advance

        • 1. Re: multiple lines with same xml tags
          mNem
          with t (xmlcontent) as 
          (
          select q'[<?xml version='1.0' encoding='UTF-8'?>  
          <S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">  
          <S:Body>
          <ns2:xyzResponse xmlns:ns2="http://xyz.xyz">  
          <return>  
              <resCode>0</resCode>  
              <resDef>SUCCESS</resDef>  
              <RecordResult>  
                  <no>1</no>  
                  <resCode>0</resCode>  
                  <resDef>SUCCESS</resDef>  
                  <refNo>191206011906</refNo>  
                  <no>2</no>  
                  <resCode>-1</resCode>  
                  <resDef>FAILURE</resDef>  
                  <refNo>191206011907</refNo>  
              </RecordResult>  
          </return>  
          </ns2:xyzResponse>  
          </S:Body>  
          </S:Envelope>]' from dual
          )
          select t1.rescode,t1.resdef, t2.* 
          from t, xmltable (
            xmlnamespaces (
              'http://schemas.xmlsoap.org/soap/envelope/' as "S"
              ,'http://xyz.xyz' as "ns2"
            )
            ,
            '/S:Envelope/S:Body/ns2:xyzResponse/return'
            passing xmltype(t.xmlcontent)
            columns
              rescode number path 'resCode',
              resdef varchar2(15) path 'resDef',
              recordresult xmltype path 'RecordResult'
          ) t1
          left join
          xmltable(
            '/RecordResult/no'
            passing t1.recordresult
            columns
              nmbr number           path '.',
              rescode1 number       path './following-sibling::*[1]',
              resdef1 varchar2(20)  path './following-sibling::*[2]',
              refno varchar2(20)    path './following-sibling::*[3]'
          ) t2
          on 0=0
          ;
          


             RESCODE RESDEF                NMBR   RESCODE1 RESDEF1              REFNO              
          ---------- --------------- ---------- ---------- -------------------- --------------------
                   0 SUCCESS                  1          0 SUCCESS              191206011906       
                   0 SUCCESS                  2         -1 FAILURE              191206011907       

           

           

           

          ASSUMPTION: each <no> is followed by the required siblings namely <resCode>, <resDef> and <refNo> etc.

           

          EDIT: Modified the typo and columns that shared the same name. The where clause 1=1 is replaced by 11=1 for some reason by the editor.

          • 2. Re: multiple lines with same xml tags
            mNem

            select t1.rescode,t1.resdef, t2.*

            from t, xmltable (

              xmlnamespaces (

                'http://schemas.xmlsoap.org/soap/envelope/' as "S"

                ,'http://xyz.xyz' as "ns2"

              )

              ,

              '/S:Envelope/S:Body/ns2:xyzResponse/return'

              passing xmltype(t.xmlcontent)

              columns

                rescode number path 'resCode',

                resdef varchar2(15) path 'resDef',

                recordresult xmltype path 'RecordResult'

            ) t1

            left join

            xmltable(

              '/RecordResult/no'

              passing t1.recordresult

              columns

                nmbr number           path '.',

                rescode1 number       path './following-sibling::*[1][local-name() = "resCode"]',

                resdef1 varchar2(20)  path './following-sibling::*[2][local-name() = "resDef"]',

                refno varchar2(20)    path './following-sibling::*[3][local-name() = "refNo"]'

            ) t2

            on 1=1

            ;

             

            Asserts if the siblings are in a particular order, i.e., first following sibling is of <resCode>, second sibling is of <resDef> and so on.

            1 person found this helpful
            • 3. Re: multiple lines with same xml tags
              cormaco

              You can also do this:

               

              rescode1 number   path './following-sibling::resCode[1]', 

              resdef1 varchar2(20)  path './following-sibling::resDef[1]', 

              refno varchar2(20)path './following-sibling::refNo[1]' 
              1 person found this helpful
              • 4. Re: multiple lines with same xml tags
                2799196

                Good morning

                 

                mNem, words cannot express how I feel now. Thank you very much! It returns the data just the way I wanted to have