How to parse xml with PL/SQL correctly

Question

I got an xml response, and I have to parseit using PL/SQL.

This is my XML:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"         
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
  <GetFacturasClienteResponse xmlns="http://tempuri.org/">
     <GetFacturasClienteResult>
        <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
           <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="table" msdata:UseCurrentLocale="true">
              <xs:complexType>
                 <xs:choice minOccurs="0" maxOccurs="unbounded">
                    <xs:element name="table">
                       <xs:complexType>
                          <xs:sequence>
                             <xs:element name="NUM_FACTURA" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="30"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="NPE" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="26"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="TIPO_DOC" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="3"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="FECHA_VENCIMIENTO" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="10"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="SALDO_ACTUAL" type="xs:decimal" minOccurs="0"/>
                             <xs:element name="SALDO_ACTUAL_SC" type="xs:decimal" minOccurs="0"/>
                             <xs:element name="TIPO_FACTURA" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="2"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="DIAS_DIF" type="xs:decimal" minOccurs="0"/>
                             <xs:element name="TOTAL_CANCELADO" type="xs:decimal" minOccurs="0"/>
                             <xs:element name="LINEA_PREPAGO" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="2"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="CICLO" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="2"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                             <xs:element name="NOMBRE_CLIENTE" minOccurs="0">
                                <xs:simpleType>
                                   <xs:restriction base="xs:string">
                                      <xs:maxLength value="40"/>
                                   </xs:restriction>
                                </xs:simpleType>
                             </xs:element>
                          </xs:sequence>
                       </xs:complexType>
                    </xs:element>
                 </xs:choice>
              </xs:complexType>
           </xs:element>
        </xs:schema>
        <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
           <DocumentElement xmlns="">
              <table diffgr:id="table1" msdata:rowOrder="0">
                 <NUM_FACTURA>047-001-01-10640742</NUM_FACTURA>
                 <NPE>001-01-10640742</NPE>
                 <TIPO_DOC>047</TIPO_DOC>
                 <FECHA_VENCIMIENTO>08/05/2017</FECHA_VENCIMIENTO>
                 <SALDO_ACTUAL>271.54</SALDO_ACTUAL>
                 <SALDO_ACTUAL_SC>271.54</SALDO_ACTUAL_SC>
                 <DIAS_DIF>11</DIAS_DIF>
                 <TOTAL_CANCELADO>0</TOTAL_CANCELADO>
                 <CICLO>48</CICLO>
                 <NOMBRE_CLIENTE>RED TECNOLOGICA ARPU</NOMBRE_CLIENTE>
              </table>
              <table diffgr:id="table2" msdata:rowOrder="1">
                 <NUM_FACTURA>047-001-01-10793193</NUM_FACTURA>
                 <NPE>001-01-10793193</NPE>
                 <TIPO_DOC>047</TIPO_DOC>
                 <FECHA_VENCIMIENTO>06/06/2017</FECHA_VENCIMIENTO>
                 <SALDO_ACTUAL>271.72</SALDO_ACTUAL>
                 <SALDO_ACTUAL_SC>271.72</SALDO_ACTUAL_SC>
                 <DIAS_DIF>-18</DIAS_DIF>
                 <TOTAL_CANCELADO>0</TOTAL_CANCELADO>
                 <CICLO>48</CICLO>
                 <NOMBRE_CLIENTE>RED TECNOLOGICA ARPU</NOMBRE_CLIENTE>
              </table>
           </DocumentElement>
        </diffgr:diffgram>
     </GetFacturasClienteResult>
  </GetFacturasClienteResponse>

This is my PL/SQL code:

create or replace PROCEDURE          "GETFACTURASCLIENTEPOSPAGO" 
(origenpago in varchar2, 
telefono in varchar2, 
corrbanco in varchar2,
p_recordset     out sys_refcursor) as
soap_request VARCHAR2(30000);
soap_respond CLOB; --VARCHAR2(32000);
--soap_respond varchar2(30000);

http_req utl_http.req;
http_resp utl_http.resp;
resp XMLType;
resp1 CLOB;
--resp1        varchar2(30000);
resp2      VARCHAR2(30000);
soap_err   EXCEPTION;
v_code     VARCHAR2(200);
v_msg      VARCHAR2(1800);
v_len      NUMBER;
v_txt      VARCHAR2(32767);
i          INTEGER;
tablaTemp  VARCHAR2(2000);
tablaTemp2 VARCHAR2(2000);
tablaTemp3 VARCHAR2(2000);
tablaTemp4 VARCHAR2(2000);
tablaTemp5 VARCHAR2(2000);
tablaTemp6 VARCHAR2(2000);
--p_recordset sys_refcursor;
vCampo CLOB;
buffer    VARCHAR2(32767);
tramaenee VARCHAR2(384);
BEGIN
soap_request := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05      
/soap-envelope" xmlns:tem="http://tempuri.org/">
<soap:Header/>
<soap:Body>
  <tem:GetFacturasCliente>
     <tem:cod_pais>504</tem:cod_pais>
     <tem:cod_banco>11</tem:cod_banco>
     <tem:origen_pago>'||origenpago||'</tem:origen_pago>
     <tem:tipo_producto>02</tem:tipo_producto>
     <tem:num_browse></tem:num_browse>
     <tem:telefono>'||telefono||'</tem:telefono>
     <tem:corr_banco>'||corrbanco||'</tem:corr_banco>
  </tem:GetFacturasCliente>
</soap:Body>
</soap:Envelope>';
-- dbms_output.put_line('Paso 1' || soap_request);
http_req := utl_http.begin_request('http://172.28.2.72:4909   
/ExternListenerWs.asmx', 'POST', 'HTTP/1.1');
--utl_http.set_header(http_req, 'Content-Type',   
'application/soap+xml;charset=UTF-8');
 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
--UTL_HTTP.SET_HEADER(http_req, 'User-Agent', 'Mozilla/4.0');
--utl_http.set_header(http_req, 'Content-Type',   
'application/soap+xml;charset=UTF-8');
utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
utl_http.set_header(http_req, 'SOAPAction', 'http://tempuri.org  
/GetFacturasCliente');
dbms_output.put_line('Request : '||soap_request);
utl_http.write_text(http_req, soap_request);
http_resp := utl_http.get_response(http_req);
/*
SELECT EXTRACTVALUE(http_resp
              , 'ns2:strPasthrResponse'
              , 'xmlns:ns2="http://dom.jet.com/"') 
 INTO l_response_result
 FROM dual;
 DBMS_OUTPUT.put_line ( 'Result> NonExistingWaybills=' ||     
 l_response_result);

*/

DBMS_LOB.CREATETEMPORARY(vCampo, true);
LOOP
 BEGIN
  utl_http.read_text(http_resp, buffer, 32767);
  IF buffer IS NOT NULL AND LENGTH(buffer) > 0 THEN
    dbms_lob.writeappend(vCampo, LENGTH(buffer), buffer);
  END IF;
EXCEPTION
WHEN OTHERS THEN
  EXIT;
END;
END LOOP;
dbms_output.put_line('Respuesta : ' || vCampo);
--utl_http.read_text(http_resp, soap_respond);
utl_http.end_response(http_resp);
OPEN p_recordset FOR
WITH XML AS
(SELECT XMLTYPE(vCampo) AS OBJECT_VALUE FROM dual
)
select x.*
from XML,
XMLTABLE 
( XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",
'http://www.w3.org/2001/XMLSchema' as "xs",
'urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr",
default 'http://tempuri.org/'),
 '*:Envelope/*:Body/GetFacturasClienteResponse'  
 --'*:Envelope/*:Body/GetFacturasClienteResponse/GetFacturasClienteResult'  
passing OBJECT_VALUE 
columns  
GetFacturasClienteResult varchar2(1000) path './GetFacturasClienteResult',
GetFacturasClienteResult1 varchar2(100) path './xs:schema/diffgr:diffgram     
/DocumentElement/table/NUM_FACTURA'
 )  x;


END GETFACTURASCLIENTEPOSPAGO;

I'm getting this result:

GetFacturasClienteResult.
047-001-01-10640742001-01-1064074204708/05/2017271.54271.5411048RED TECNOLOGICA ARPU047-001-01-10793193001-01-1079319304706/06/2017271.72271.72-18048RED TECNOLOGICA ARPU

but what I need is this values :

    NUM_FACTURA        NPE      FECHA_VENCIMIENTO     NOMBRE_CLIENTE
047-001-01-10640742   001-01-10640742     08/05/2017       RED TECNOLOGICA ARPU 
047-001-01-10793193   001-01-10793193      06/06/2017        RED TECNOLOGICA ARPU

Can anybody help me. Thank you in advance.

Boneist · Accepted Answer · 2017-05-19 13:22:49Z

It's possible to do this in SQL:

WITH sd AS (SELECT XMLTYPE('<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"         
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetFacturasClienteResponse xmlns="http://tempuri.org/">
<GetFacturasClienteResult>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="table" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="table">
<xs:complexType>
<xs:sequence>
<xs:element name="NUM_FACTURA" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NPE" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="26"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="TIPO_DOC" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="3"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FECHA_VENCIMIENTO" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SALDO_ACTUAL" type="xs:decimal" minOccurs="0"/>
<xs:element name="SALDO_ACTUAL_SC" type="xs:decimal" minOccurs="0"/>
<xs:element name="TIPO_FACTURA" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DIAS_DIF" type="xs:decimal" minOccurs="0"/>
<xs:element name="TOTAL_CANCELADO" type="xs:decimal" minOccurs="0"/>
<xs:element name="LINEA_PREPAGO" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CICLO" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="NOMBRE_CLIENTE" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="40"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
           <DocumentElement xmlns="">
              <table diffgr:id="table1" msdata:rowOrder="0">
                 <NUM_FACTURA>047-001-01-10640742</NUM_FACTURA>
                 <NPE>001-01-10640742</NPE>
                 <TIPO_DOC>047</TIPO_DOC>
                 <FECHA_VENCIMIENTO>08/05/2017</FECHA_VENCIMIENTO>
                 <SALDO_ACTUAL>271.54</SALDO_ACTUAL>
                 <SALDO_ACTUAL_SC>271.54</SALDO_ACTUAL_SC>
                 <DIAS_DIF>11</DIAS_DIF>
                 <TOTAL_CANCELADO>0</TOTAL_CANCELADO>
                 <CICLO>48</CICLO>
                 <NOMBRE_CLIENTE>RED TECNOLOGICA ARPU</NOMBRE_CLIENTE>
              </table>
              <table diffgr:id="table2" msdata:rowOrder="1">
                 <NUM_FACTURA>047-001-01-10793193</NUM_FACTURA>
                 <NPE>001-01-10793193</NPE>
                 <TIPO_DOC>047</TIPO_DOC>
                 <FECHA_VENCIMIENTO>06/06/2017</FECHA_VENCIMIENTO>
                 <SALDO_ACTUAL>271.72</SALDO_ACTUAL>
                 <SALDO_ACTUAL_SC>271.72</SALDO_ACTUAL_SC>
                 <DIAS_DIF>-18</DIAS_DIF>
                 <TOTAL_CANCELADO>0</TOTAL_CANCELADO>
                 <CICLO>48</CICLO>
                 <NOMBRE_CLIENTE>RED TECNOLOGICA ARPU</NOMBRE_CLIENTE>
              </table>
           </DocumentElement>
        </diffgr:diffgram>
     </GetFacturasClienteResult>
  </GetFacturasClienteResponse>
</soap:Body>
</soap:Envelope>') x FROM dual)
SELECT xmldata.*
FROM sd
     CROSS JOIN XMLTABLE('//DocumentElement/table' PASSING sd.x
                         COLUMNS num_factura VARCHAR2(30) PATH 'NUM_FACTURA',
                                 npe VARCHAR2(26) PATH 'NPE',
                                 fecha_vencimiento VARCHAR2(10) PATH 'FECHA_VENCIMIENTO',
                                 nombr_cliente VARCHAR2(40) PATH 'NOMBRE_CLIENTE') xmldata;

NUM_FACTURA                    NPE                        FECHA_VENCIMIENTO NOMBR_CLIENTE
------------------------------ -------------------------- ----------------- ----------------------------------------
047-001-01-10640742            001-01-10640742            08/05/2017        RED TECNOLOGICA ARPU
047-001-01-10793193            001-01-10793193            06/06/2017        RED TECNOLOGICA ARPU

Arkadiusz Łukasiewicz · Accepted Answer · 2017-05-19 13:31:02Z

Your example XML is not valid, There are not 2 closing tag.

create test_clob (a clob) ;
-- src xml is inserted into test_clob  table
with xml_src as (
select diffgram, xsSchema inot 
from test_clob,
XMLTABLE 
( XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",
'http://www.w3.org/2001/XMLSchema' as "xs",
'urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr",
default 'http://tempuri.org/'),
 '/soap:Envelope/soap:Body/GetFacturasClienteResponse/GetFacturasClienteResult'  
 --'*:Envelope/*:Body/GetFacturasClienteResponse/GetFacturasClienteResult'  
passing xmltype(a) 
columns  
diffgram xmltype path './diffgr:diffgram',
xsSchema xmltype path './xs:schema') )
select s.* from xml_src,xmltable( XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr"
, 'urn:schemas-microsoft-com:xml-msdata'  as "msdata"
    ),'/diffgr:diffgram/DocumentElement/table'
 passing diffgram
 columns 

  NUM_FACTURA varchar2 (100) path 'NUM_FACTURA' ,
  NPE varchar2 (100) path 'NPE' ,
  TIPO_DOC varchar2 (100) path 'TIPO_DOC',
  diffgr_id varchar2 (100) path '@diffgr:id',
  -- ...more column 
   rowOrder varchar2 (100) path '@msdata:rowOrder'
 ) s

The problem is your xml has more than one default namespace.

GetFacturasClienteResponse is in http://tempuri.org/

DocumentElement is in default standard namespace

Collectives™ on Stack Overflow

How to parse xml with PL/SQL correctly

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related