Thanks for the question, Morten.

Asked: October 17, 2017 - 11:19 am UTC

Last updated: November 01, 2017 - 1:06 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Any idea why I get the error message "LPX-00242: invalid use of ampersand ('&') character (use &)" even though the ampersand in the below test case is already escaped? Tested with same result both in 11g XE, 11.2.0.4 SE, and on apex.oracle.com (which runs 12.2?).

set define off;

select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<soapenv:Header>
<SourceEndpoint>ABC</SourceEndpoint>
<DestinationEndpoint>ABC</DestinationEndpoint>
</soapenv:Header>
<soapenv:Body>
<ser:AVAAPInvoicesServiceCreateRequest>
<ava:AVAAPInvoices>
<ava:LedgerJournalTable class="entity">
<ava:LedgerJournalTrans class="entity">
<ava:Name>ABC Foo Bar</ava:Name>
<ava:Txt>ABC Foo &amp; Bar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
</ava:LedgerJournalTable>
</ava:AVAAPInvoices>
</ser:AVAAPInvoicesServiceCreateRequest>
</soapenv:Body>
</soapenv:Envelope>').transform(xmltype('<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="//ava:LedgerJournalTable[1]">
Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
<xsl:text>
=====================================
</xsl:text>
</xsl:for-each>
<xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
<xsl:value-of select="ava:Txt"/>
<xsl:text>
-------------------------------------
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>'))
from dual;


ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 2
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.

and Connor said...

I got this from Mark Drake, the XMLDB PM

The workaround is to add a getClobVal(), which prevents us from assuming the output is valid XML..

SQL> select xmltype(
  2  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
  3            xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services"
  4            xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
  5  <soapenv:Header>
  6  <SourceEndpoint>ABC</SourceEndpoint>
  7  <DestinationEndpoint>ABC</DestinationEndpoint>
  8  </soapenv:Header>
  9  <soapenv:Body>
10  <ser:AVAAPInvoicesServiceCreateRequest>
11  <ava:AVAAPInvoices>
12  <ava:LedgerJournalTable class="entity">
13  <ava:LedgerJournalTrans class="entity">
14  <ava:Name>ABC Foo Bar</ava:Name>
15  <ava:Txt>ABC Foo &amp; Bar</ava:Txt>
16  </ava:LedgerJournalTrans>
17  <ava:LedgerJournalTrans class="entity">
18  <ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
19  </ava:LedgerJournalTrans>
20  <ava:LedgerJournalTrans class="entity">
21  <ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
22  </ava:LedgerJournalTrans>
23  </ava:LedgerJournalTable>
24  </ava:AVAAPInvoices>
25  </ser:AVAAPInvoicesServiceCreateRequest>
26  </soapenv:Body>
27  </soapenv:Envelope>').transform(
28     xmltype(
29     '<?xml version="1.0"?>
30     <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
31          xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services"
32          xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
33  <xsl:output method="text"/>
34  <xsl:template match="/">
35  <xsl:for-each select="//ava:LedgerJournalTable[1]">
36  Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
37  <xsl:text>
38  =====================================
39  </xsl:text>
40  </xsl:for-each>
41  <xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
42  <xsl:value-of select="ava:Txt"/>
43  <xsl:text>
44  -------------------------------------
45  </xsl:text>
46  </xsl:for-each>
47  </xsl:template>
48  </xsl:stylesheet>')).getClobVal()
49  from dual;

XMLTYPE('<SOAPENV:ENVELOPEXMLNS:SOAPENV="HTTP://SCHEMAS.XMLSOAP.ORG/SOAP/ENVELOP
--------------------------------------------------------------------------------

Title        : ABC Foo & Bar
=====================================
ABC Foo & Ba

Rating

(2 ratings)

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

Comments

Still fails in PL/SQL

Morten Braten, October 19, 2017 - 7:55 am UTC

Trying to do the equivalent in PL/SQL, but this still fails:

set define off;
set serveroutput on size unlimited;

declare
  l_stylesheet_string varchar2(32000) := '<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="//ava:LedgerJournalTable[1]">
Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
<xsl:text>
=====================================
</xsl:text>
</xsl:for-each>
<xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
<xsl:value-of select="ava:Txt"/>
<xsl:text>
-------------------------------------
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>';

  l_xml xmltype := xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<soapenv:Header>
<SourceEndpoint>ABC</SourceEndpoint>
<DestinationEndpoint>ABC</DestinationEndpoint>
</soapenv:Header>
<soapenv:Body>
<ser:AVAAPInvoicesServiceCreateRequest>
<ava:AVAAPInvoices>
<ava:LedgerJournalTable class="entity">
<ava:LedgerJournalTrans class="entity">
<ava:Name>ABC Foo Bar</ava:Name>
<ava:Txt>ABC Foo &amp; Bar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
</ava:LedgerJournalTable>
</ava:AVAAPInvoices>
</ser:AVAAPInvoicesServiceCreateRequest>
</soapenv:Body>
</soapenv:Envelope>');

begin
  dbms_output.put_line ('starting...');
  dbms_output.put_line (substr(l_xml.transform (xmltype(l_stylesheet_string)).getclobval(),1,32000));
  dbms_output.put_line ('... finished');
end;

I get the following output:

starting...


Error starting at line : 4 in command -
declare
  l_stylesheet_string varchar2(32000) := '<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="//ava:LedgerJournalTable[1]">
Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
<xsl:text>
=====================================
</xsl:text>
</xsl:for-each>
<xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
<xsl:value-of select="ava:Txt"/>
<xsl:text>
-------------------------------------
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>';

  l_xml xmltype := xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<soapenv:Header>
<SourceEndpoint>ABC</SourceEndpoint>
<DestinationEndpoint>ABC</DestinationEndpoint>
</soapenv:Header>
<soapenv:Body>
<ser:AVAAPInvoicesServiceCreateRequest>
<ava:AVAAPInvoices>
<ava:LedgerJournalTable class="entity">
<ava:LedgerJournalTrans class="entity">
<ava:Name>ABC Foo Bar</ava:Name>
<ava:Txt>ABC Foo &amp; Bar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
</ava:LedgerJournalTable>
</ava:AVAAPInvoices>
</ser:AVAAPInvoicesServiceCreateRequest>
</soapenv:Body>
</soapenv:Envelope>');

begin
  dbms_output.put_line ('starting...');
  dbms_output.put_line (substr(l_xml.transform (xmltype(l_stylesheet_string)).getclobval(),1,32000));
  dbms_output.put_line ('... finished');
end;
Error report -
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 2
ORA-06512: at "SYS.XMLTYPE", line 138
ORA-06512: at line 47
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.

Strangely, if I separate the calls to do the transformation and the .getClobVal(), the transformation actually succeeds while the getClobVal() fails:

set define off;
set serveroutput on size unlimited;

declare
  l_stylesheet_string varchar2(32000) := '<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="//ava:LedgerJournalTable[1]">
Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
<xsl:text>
=====================================
</xsl:text>
</xsl:for-each>
<xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
<xsl:value-of select="ava:Txt"/>
<xsl:text>
-------------------------------------
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>';

  l_xml xmltype := xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<soapenv:Header>
<SourceEndpoint>ABC</SourceEndpoint>
<DestinationEndpoint>ABC</DestinationEndpoint>
</soapenv:Header>
<soapenv:Body>
<ser:AVAAPInvoicesServiceCreateRequest>
<ava:AVAAPInvoices>
<ava:LedgerJournalTable class="entity">
<ava:LedgerJournalTrans class="entity">
<ava:Name>ABC Foo Bar</ava:Name>
<ava:Txt>ABC Foo &amp; Bar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
</ava:LedgerJournalTable>
</ava:AVAAPInvoices>
</ser:AVAAPInvoicesServiceCreateRequest>
</soapenv:Body>
</soapenv:Envelope>');

begin
  dbms_output.put_line ('starting...');
  l_xml := l_xml.transform (xmltype(l_stylesheet_string));
  dbms_output.put_line ('did the transformation');
  dbms_output.put_line (substr(l_xml.getclobval(),1,32000));
  dbms_output.put_line ('... finished');
end;

This gives:

starting...
did the transformation


Error starting at line : 4 in command -
declare
  l_stylesheet_string varchar2(32000) := '<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="//ava:LedgerJournalTable[1]">
Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
<xsl:text>
=====================================
</xsl:text>
</xsl:for-each>
<xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
<xsl:value-of select="ava:Txt"/>
<xsl:text>
-------------------------------------
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>';

  l_xml xmltype := xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services" xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
<soapenv:Header>
<SourceEndpoint>ABC</SourceEndpoint>
<DestinationEndpoint>ABC</DestinationEndpoint>
</soapenv:Header>
<soapenv:Body>
<ser:AVAAPInvoicesServiceCreateRequest>
<ava:AVAAPInvoices>
<ava:LedgerJournalTable class="entity">
<ava:LedgerJournalTrans class="entity">
<ava:Name>ABC Foo Bar</ava:Name>
<ava:Txt>ABC Foo &amp; Bar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
<ava:LedgerJournalTrans class="entity">
<ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
</ava:LedgerJournalTrans>
</ava:LedgerJournalTable>
</ava:AVAAPInvoices>
</ser:AVAAPInvoicesServiceCreateRequest>
</soapenv:Body>
</soapenv:Envelope>');

begin
  dbms_output.put_line ('starting...');
  l_xml := l_xml.transform (xmltype(l_stylesheet_string));
  dbms_output.put_line ('did the transformation');
  dbms_output.put_line (substr(l_xml.getclobval(),1,32000));
  dbms_output.put_line ('... finished');
end;
Error report -
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 2
ORA-06512: at "SYS.XMLTYPE", line 138
ORA-06512: at line 49
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.

October 20, 2017 - 1:25 am UTC

Yeah...dont use PLSQL. No future in that language :-)

Just kidding.

Here's a (not great) workaround - I'll ask the XML PM for more details

SQL> set define off;
SQL>
SQL> declare
  2
  3  l_stylesheet_string xmltype :=
  4  xmltype(
  5     '<?xml version="1.0"?>
  6     <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  7          xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services"
  8          xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
  9  <xsl:output method="text"/>
 10  <xsl:template match="/">
 11  <xsl:for-each select="//ava:LedgerJournalTable[1]">
 12  Title        : <xsl:value-of select="ava:LedgerJournalTrans[1]/ava:Txt"/>
 13  <xsl:text>
 14  =====================================
 15  </xsl:text>
 16  </xsl:for-each>
 17  <xsl:for-each select="//ava:LedgerJournalTable/ava:LedgerJournalTrans">
 18  <xsl:value-of select="ava:Txt"/>
 19  <xsl:text>
 20  -------------------------------------
 21  </xsl:text>
 22  </xsl:for-each>
 23  </xsl:template>
 24  </xsl:stylesheet>');
 25
 26
 27  l_xml xmltype :=
 28
 29  xmltype(
 30  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
 31            xmlns:ser="http://schemas.microsoft.com/dynamics/2008/01/services"
 32            xmlns:ava="http://schemas.microsoft.com/dynamics/2008/01/documents/AVAAPInvoices">
 33  <soapenv:Header>
 34  <SourceEndpoint>ABC</SourceEndpoint>
 35  <DestinationEndpoint>ABC</DestinationEndpoint>
 36  </soapenv:Header>
 37  <soapenv:Body>
 38  <ser:AVAAPInvoicesServiceCreateRequest>
 39  <ava:AVAAPInvoices>
 40  <ava:LedgerJournalTable class="entity">
 41  <ava:LedgerJournalTrans class="entity">
 42  <ava:Name>ABC Foo Bar</ava:Name>
 43  <ava:Txt>ABC Foo &amp; Bar</ava:Txt>
 44  </ava:LedgerJournalTrans>
 45  <ava:LedgerJournalTrans class="entity">
 46  <ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
 47  </ava:LedgerJournalTrans>
 48  <ava:LedgerJournalTrans class="entity">
 49  <ava:Txt>ABC Sep&apos;17 FooBar</ava:Txt>
 50  </ava:LedgerJournalTrans>
 51  </ava:LedgerJournalTable>
 52  </ava:AVAAPInvoices>
 53  </ser:AVAAPInvoicesServiceCreateRequest>
 54  </soapenv:Body>
 55  </soapenv:Envelope>');
 56
 57  l_result clob;
 58  begin
 59    execute immediate 'select XMLtransform(:l_xml,:l_stylesheet_string).getclobval() from dual'  into l_result using l_xml, l_stylesheet_string;
 60  end;
 61  /

PL/SQL procedure successfully completed.

SQL>
SQL>

Is this a bug?

Morten Braten, October 30, 2017 - 10:32 am UTC

So is there a reason it's behaving this way in PL/SQL (throwing the error), or should it be considered a bug?

November 01, 2017 - 1:06 am UTC

I'd suspect its a bug.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Still fails in PL/SQL

Is this a bug?

More to Explore

Design

Get "LPX-00242: invalid use of ampersand" from xmltype() even though ampersand is already escaped

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Still fails in PL/SQL

Is this a bug?

More to Explore

Design