Skip to Main Content
  • Questions
  • Select XMLQuery XML parsing error with ampersands

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Francisco Carlos.

Asked: May 12, 2022 - 2:59 pm UTC

Last updated: May 16, 2022 - 2:11 am UTC

Version: 19C

Viewed 1000+ times

You Asked

Hi Tom and Team,

I guess that this issue is related to the namespace, but as I don´t know well this, Could you help me
to solve the error running this Select, please?

with testTable as 
   ( select xmltype
    ('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
      <soap:Body>  
      <ns5:MT_Consulta_pedidos_pagamento xmlns:ns2="urn:Cpy.com/Model/ConsultaPedidosDevolucao/v0" 
      xmlns:ns3="urn:Cpy.com/Model/AtualizaStatusPagamento/v0" 
      xmlns:ns4="urn:Cpy.com/Model/AtualizaItensDevolvidosCancelados/v0" 
      xmlns:ns5="urn:Cpy.com/Model/ConsultaPedidosPagamento/v0"> 
      <codigo_empresa>&Empresa</codigo_empresa> 
      <numero_pedido_venda>&Pedido</numero_pedido_venda> 
      <codigo_loja>&Loja</codigo_loja> 
      <numero_componente>&Componente</numero_componente> 
      </ns5:MT_Consulta_pedidos_pagamento> 
      </soap:Body>    
      </soap:Envelope>'
     ) xml_val from dual
    ) 
select xmlquery('/soap' 
            passing xml_val returning content) as dados 
from testTable;

and Chris said...

The problem is the ampersands - these are special characters in XML. Replace these with &amp;

Also the path /soap matches nothing!

The root element is Envelope in the soap namespace. You may find it easier to use XMLTable which allows you to define namespaces and list out the paths to the elements you want in the columns:

with testTable as 
   ( select xmltype
    ('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
      <soap:Body>  
      <ns5:MT_Consulta_pedidos_pagamento xmlns:ns2="urn:Cpy.com/Model/ConsultaPedidosDevolucao/v0" 
      xmlns:ns3="urn:Cpy.com/Model/AtualizaStatusPagamento/v0" 
      xmlns:ns4="urn:Cpy.com/Model/AtualizaItensDevolvidosCancelados/v0" 
      xmlns:ns5="urn:Cpy.com/Model/ConsultaPedidosPagamento/v0"> 
      <codigo_empresa>&amp;Empresa</codigo_empresa> 
      <numero_pedido_venda>&amp;Pedido</numero_pedido_venda> 
      <codigo_loja>&amp;Loja</codigo_loja> 
      <numero_componente>&amp;Componente</numero_componente> 
      </ns5:MT_Consulta_pedidos_pagamento> 
      </soap:Body>    
      </soap:Envelope>'
     ) xml_val from dual
    ) 
select x.envelope.getStringVal()
from testTable, xmltable (
  xmlnamespaces(default 'http://schemas.xmlsoap.org/soap/envelope/'),
  '/'
  passing xml_val columns 
    envelope xmltype path '/Envelope'
) x;

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <ns5:MT_Consulta_pedidos_pagamento xmlns:ns5="urn:Cpy.com/Model/ConsultaPedidosPagamento/v0" xmlns:ns2="urn:Cpy.com/Model/ConsultaPedidosDevolucao/v0" xmlns:ns3="urn:Cpy.com/Model/AtualizaStatusPagamento/v0" xmlns:ns4="urn:Cpy.com/Model/AtualizaItensDevolvidosCancelados/v0">
      <codigo_empresa>&amp;Empresa</codigo_empresa>
      <numero_pedido_venda>&amp;Pedido</numero_pedido_venda>
      <codigo_loja>&amp;Loja</codigo_loja>
      <numero_componente>&amp;Componente</numero_componente>
    </ns5:MT_Consulta_pedidos_pagamento>
  </soap:Body>
</soap:Envelope>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reading TAGs from a XMLTYPE field

Francisco Moya, May 13, 2022 - 8:08 pm UTC

Thank you,

It´s worked fine as expected
Connor McDonald
May 16, 2022 - 2:11 am UTC

Glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.