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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to parse XML from PL/SQL

jeffreehy-JavaNetMar 23 2019 — edited Mar 24 2019

Hi all.

I'm trying to parse an xml response from PL/SQL but not getting the fields back.

This is my xml:

<?xml version='1.0' encoding='UTF-8'?>

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

<S:Body>

<ns2:ConsultaEEHResponse xmlns:ns2="http://jeta.servicios.ws/">

<return>

<codigoError>6</codigoError>

<mensaje>El Banco se encuentra Inactivo para Recaudo.</mensaje>

<resultado>false</resultado>

</return>

</ns2:ConsultaEEHResponse>

</S:Body></S:Envelope>

This is my snippet PL/SQL code:

open p_recordset for

    with XML as

     (select XMLTYPE(vCampo1) as OBJECT_VALUE from dual)

    select *

      from XML,

           XMLTABLE(xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "S",

                           default  'http://jeta.servicios.ws/'),  --  as "ns2",

                                --default 'http://schemas.servicestack.net/types'),

                   '//*:return'

                   passing OBJECT_VALUE columns

                   "codigoError" varchar2(5) PATH 'codigoError',

                   "mensaje" varchar2(200) PATH 'mensaje',

                   "Resultado" varchar2(5) PATH 'resultado') ;

end CONSULTA_EEH;

please help me. Thank you very much.
This post has been answered by mNem on Mar 24 2019
Jump to Answer

Comments

mNem
Answer 
with t (vCampo1) as 
(
select 
'<?xml version="1.0" encoding="UTF-8"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:ConsultaEEHResponse xmlns:ns2="http://jeta.servicios.ws/">
<return>
<codigoError>6</codigoError>
<mensaje>El Banco se encuentra Inactivo para Recaudo.</mensaje>
<resultado>false</resultado>
</return>
</ns2:ConsultaEEHResponse>
</S:Body>
</S:Envelope>' 
from dual
)
select a.*
from t,
XMLTABLE(
    xmlnamespaces(
      'http://schemas.xmlsoap.org/soap/envelope/' as "S",
      'http://jeta.servicios.ws/'                 as "ns2"
    ),
     '/S:Envelope/S:Body/ns2:ConsultaEEHResponse/return'
     passing xmltype(vCampo1) 
     columns 
     codigoError varchar2(5) PATH 'codigoError',
     mensaje varchar2(200) PATH 'mensaje',
     Resultado varchar2(5) PATH 'resultado'
)a 
;
Marked as Answer by jeffreehy-JavaNet · Sep 27 2020
1 person found this helpful
jeffreehy-JavaNet

Yes. Thank you very much, I try similar way, but I missed one ")" so a got compilation error. Some time you got the answer in front of you but got blind. Appreciate your help.

1 - 2

Post Details

Added on Mar 23 2019
#pl-sql-xml-programming, #xml
2 comments
2,758 views