7 Replies Latest reply on Aug 16, 2018 12:42 PM by 3219190

    HOW CAN I EXTRACT THE VALUE FROM xml

    3219190

      HOW CAN I EXTRACT THE VALUE FROM THE LABEL  transactionId OF THE NEXT xml

       

       

      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

         <soap:Body>

            <ns2:UploadResponse xmlns:ns2="http://invoice.carvajal.com/invoiceService/">

               <status>Archivo recibido con exito.</status>

               <transactionId>942e6c02bf6d47c0b9a57969bedc058d</transactionId>

            </ns2:UploadResponse>

         </soap:Body>

      </soap:Envelope>

       

      y try

       

      select   XMLQUERY ('/soap/soap/ns2/transactionId/text()'

      PASSING

      XMLTYPE(

      '

      <soap>

         <soap:Body>

            <ns2:UploadResponse xmlns:ns2="http://invoice.carvajal.com/invoiceService/">

               <status>Archivo recibido con exito.</status>

               <transactionId>942e6c02bf6d47c0b9a57969bedc058d</transactionId>

            </ns2:UploadResponse>

         </soap:Body>

      </soap:Envelope>')

      RETURNING CONTENT ) AS A

      from dual;

       

      retunr error

       

      ORA-31011: Fallo en el análisis de XML

      ORA-19213: se ha producido un error en el procesamiento de XML en las líneas  3

      LPX-00234: el prefijo de espacio de nombres "soap" no se ha declarado

      ORA-06512: en "SYS.XMLTYPE", línea 310

      ORA-06512: en línea 1

      31011. 00000 -  "XML parsing failed"

      *Cause:    XML parser returned an error while trying to parse the document.

      *Action:   Check if the document to be parsed is valid.

        • 1. Re: HOW CAN I EXTRACT THE VALUE FROM xml
          mNem

          Your passing query has some issues:

           

           

          select   XMLQUERY ('/soap/soap/ns2/transactionId/text()'

          PASSING

          XMLTYPE(

          '

          <soap>

             <soap:Body>

                <ns2:UploadResponse xmlns:ns2="http://invoice.carvajal.com/invoiceService/">

                   <status>Archivo recibido con exito.</status>

                   <transactionId>942e6c02bf6d47c0b9a57969bedc058d</transactionId>

                </ns2:UploadResponse>

             </soap:Body>

          </soap:Envelope>')

          RETURNING CONTENT ) AS A

          from dual;

           

           

          Here is the resolved query:

           

           

            select   XMLQUERY (
            
            '
            declare namespace soap = "http://schemas.xmlsoap.org/soap/envelope/"; (: :)
            declare namespace ns2  = "http://invoice.carvajal.com/invoiceService/"; (: :)
            /soap:Envelope/soap:Body/ns2:UploadResponse/transactionId/text()
            '
            
            PASSING
            
            XMLTYPE(
            
            '
            <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
            
               <soap:Body>
            
                  <ns2:UploadResponse xmlns:ns2="http://invoice.carvajal.com/invoiceService/">
            
                     <status>Archivo recibido con exito.</status>
            
                     <transactionId>942e6c02bf6d47c0b9a57969bedc058d</transactionId>
            
                  </ns2:UploadResponse>
            
               </soap:Body>
            
            </soap:Envelope>
            ')
            
            RETURNING CONTENT ) AS A
            
            from dual;
          
          • 2. Re: HOW CAN I EXTRACT THE VALUE FROM xml
            mNem

            Another way without the namespaces specified :

             

              select   XMLQUERY (
              
              '
              //transactionId/text()
              '
              
              PASSING
              
              XMLTYPE(
              
              '
              <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
              
                 <soap:Body>
              
                    <ns2:UploadResponse xmlns:ns2="http://invoice.carvajal.com/invoiceService/">
              
                       <status>Archivo recibido con exito.</status>
              
                       <transactionId>942e6c02bf6d47c0b9a57969bedc058d</transactionId>
              
                    </ns2:UploadResponse>
              
                 </soap:Body>
              
              </soap:Envelope>
              ')
              
              RETURNING CONTENT ) AS A
              
              from dual;
            
            • 3. Re: HOW CAN I EXTRACT THE VALUE FROM xml
              mNem

              And, if you need other values, for example status ...

               

              select a.*  from XMLTABLE (
                
                xmlnamespaces( 'http://schemas.xmlsoap.org/soap/envelope/'   as "soap", 
                               'http://invoice.carvajal.com/invoiceService/' as "ns2"),
                '
                /soap:Envelope/soap:Body/ns2:UploadResponse
                '
                
                PASSING
                
                XMLTYPE(
                
                '
                <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                
                   <soap:Body>
                
                      <ns2:UploadResponse xmlns:ns2="http://invoice.carvajal.com/invoiceService/">
                
                         <status>Archivo recibido con exito.</status>
                
                         <transactionId>942e6c02bf6d47c0b9a57969bedc058d</transactionId>
                
                      </ns2:UploadResponse>
                
                   </soap:Body>
                
                </soap:Envelope>
                ')
                
                columns
                  col1 varchar2(40) path 'transactionId'
                , col2 varchar2(40) path 'status'
              ) AS A
              ;
              

               

               

              COL1                                     COL2                                    
              ---------------------------------------- ----------------------------------------
              942e6c02bf6d47c0b9a57969bedc058d         Archivo recibido con exito.             
              
              
              • 4. Re: HOW CAN I EXTRACT THE VALUE FROM xml
                odie_63

                mNem wrote:

                 

                Another way without the namespaces specified :

                Another bad practice.

                If the path is known upfront, do not use descendant axes, it'll save Oracle from performing useless work trying to find nodes where they're not.

                 

                On a side note, when extracting a scalar value using XMLQuery, the output should be wrapped by an XMLCast call, otherwise an XMLType instance is returned and uncontrolled implicit conversion may occur.

                (removes the need for the text() accessor as well)

                 

                select XMLCast(
                        XMLQuery(
                          'declare namespace soap = "http://schemas.xmlsoap.org/soap/envelope/"; (: :)  
                          declare namespace ns2  = "http://invoice.carvajal.com/invoiceService/"; (: :)
                          /soap:Envelope/soap:Body/ns2:UploadResponse/transactionId'  
                          PASSING XMLTYPE('...')    
                          RETURNING CONTENT
                        ) 
                        as varchar2(128)
                      ) AS result
                from dual;
                
                • 5. Re: HOW CAN I EXTRACT THE VALUE FROM xml
                  mNem

                  Hi odie_63,

                   

                  I was getting there ... ( little late though ;-) )

                   

                  Note:

                  Prior to Oracle Database 11g Release 2, some users employed Oracle SQL functions extract and extractValue to do some of what can be done better using SQL/XML functions XMLQuery and XMLCast.

                  source: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/xquery-and-XML-DB.html#GUID-F4508470-D420-4B5A-B5D…

                   

                  select  XMLQUERY (
                    '/a/b/text()'
                    PASSING
                    XMLTYPE('<a><b>123abc</b></a>')
                    RETURNING CONTENT ) 
                   AS A,
                  
                   xmlcast(
                   XMLQUERY (
                    '/a/b/text()'
                    PASSING
                    XMLTYPE('<a><b>123abc</b></a>')
                    RETURNING CONTENT ) 
                    as varchar2(6))
                   AS B
                  
                  
                  
                    from dual;
                  

                   

                   

                    select  dump(
                    XMLQUERY (
                    '/a/b/text()'
                    PASSING
                    XMLTYPE('<a><b>123abc</b></a>')
                    RETURNING CONTENT ) 
                    )
                   AS A,
                  
                   dump(
                   xmlcast(
                   XMLQUERY (
                    '/a/b/text()'
                    PASSING
                    XMLTYPE('<a><b>123abc</b></a>')
                    RETURNING CONTENT ) 
                    as varchar2(6))
                    )
                   AS B
                  
                  
                  from dual;
                  
                  
                  • 6. Re: HOW CAN I EXTRACT THE VALUE FROM xml
                    mNem

                    odie_63 wrote:

                     

                    mNem wrote:

                     

                    Another way without the namespaces specified :

                    Another bad practice.

                    If the path is known upfront, do not use descendant axes, it'll save Oracle from performing useless work trying to find nodes where they're not.

                     

                    I guess I have repeated it again. Sorry.

                     

                    Thanks for correcting me.

                     

                    Best regards,

                    • 7. Re: HOW CAN I EXTRACT THE VALUE FROM xml
                      3219190

                      Thanks, very good, it also worked