This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 28, 2013 3:13 AM by odie_63 RSS

Escaped ampersand still causes LPX-00242 in xmltype.transform

pl_sequel Newbie
Currently Being Moderated
Running Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Collecting input from web form, and fetching via SQLX functions to then transform into html and plain text documents. If the "&" is inputted... the SQLX functions does escape it to "&"... but the LPX-00242 error is still raised when sending to xmltype.transform.

A simple test case:
set define off;
declare
l_xsl xmltype;
l_xml xmltype;
begin

l_xsl := xmltype('<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="/">

Hello <xsl:value-of select="//name"/>

</xsl:template>
</xsl:stylesheet>');

l_xml := xmltype('<test><name>Jane&amp;John Doe</name></test>');

dbms_output.put_line(l_xml.transform
                                    (l_xsl
                                    ).getclobval ());
end;
the above code raises this exception:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00241: entity reference is not well formed
Error at line 3
ORA-06512: at "SYS.XMLTYPE", line 138
ORA-06512: at line 20
If I rerun the above block... but change the xml string to <test><name>Jane &amp;amp; John Doe</name></test>

I now get the following exception:
Error at line 2
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 3
ORA-06512: at "SYS.XMLTYPE", line 138
ORA-06512: at line 20
If I change the xml string to <test><name>Jane &amp;amp;amp; John Doe</name></test>.. no exceptions and displays the following:

Hello Jane &amp;amp; John Doe

any reason why the SQLX function escaping is not sufficient and I need to double-escape it? The working version is not ideal, since I now have to decode the entity reference back to it's character, otherwise the entity reference code is displayed in the content.

Edited by: pl_sequel on Sep 7, 2011 2:12 PM
  • 1. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    pl_sequel Newbie
    Currently Being Moderated
    Well... found a somewhat related issue on Metalink... solution called for using dbms_xmlgen.convert to "encode" the ampersand to &amp;amp;. So, using dbms_xmlgen.convert within my SQLX xmlelement function, the resulting xml gets &amp;amp;amp; which then passes through xslt without error.

    I then have to call dbms_gen.convert with "entity_decode" on the output to restore the &amp;amp; back to "&" character.

    Seems more of a hack than a solution... if anyone has more info on this, I'd be glad to hear it.
  • 2. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    odie_63 Guru
    Currently Being Moderated
    Hi,

    I've encountered that behaviour before, I think it's a bug related to the text output method.
    but the LPX-00242 error is still raised when sending to xmltype.transform.
    Actually, the error is raised when using getClobVal on the result.

    The following seems to work without encoding/decoding entities :
    SQL> set define off
    SQL> set serveroutput on
    SQL> declare
      2   l_xsl xmltype;
      3   l_xml xmltype;
      4   res clob;
      5  begin
      6  
      7   l_xsl := xmltype('<?xml version="1.0"?>
      8  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      9  <xsl:output method="text"/>
     10  <xsl:template match="/">
     11  Hello <xsl:value-of select="//name"/>
     12  </xsl:template>
     13  </xsl:stylesheet>');
     14  
     15   select xmlelement("test", xmlelement("name", 'Jane&John Doe'))
     16   into l_xml
     17   from dual;
     18  
     19   select xmltransform(l_xml,l_xsl).getClobVal()
     20   into res
     21   from dual
     22   ;
     23  
     24   dbms_output.put_line(res);
     25  
     26  end;
     27  /
     
    
    Hello Jane&John Doe
     
    PL/SQL procedure successfully completed
     
  • 3. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    pl_sequel Newbie
    Currently Being Moderated
    Thanks... so based on that , I assume the internal implementations of XMLTRANSFORM and XMLTYPE.TRANSFORM aren't the same... I think I read somewhere in the docs that XMLTRANSFORM is the preferred method to use for transformations.

    My example was a little oversimplified... i didn't mention I was also using xsl parameters.. via the somewhat "undocumented" parammap parameter to xmltype.transform ... looking at the XMLTRANSFORM method... seems that parameter isn't supported.

    Are xsl parameters still supported in Oracle XML 11g? Is there an alternative?
  • 4. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    Jason_(A_Non) Expert
    Currently Being Moderated
    xsl parameters are supported for XMLTRANSFORM as shown via {message:id=4550828}. Look at odie_63's first post to see an example. I explored it following my reading of his post on my blog at http://anononxml.blogspot.com/2010/09/xmltypexmltransform-and-parameters.html.

    Note: The q'{....}' format he used is called a Text Literal
  • 5. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    odie_63 Guru
    Currently Being Moderated
    I assume the internal implementations of XMLTRANSFORM and XMLTYPE.TRANSFORM aren't the same...
    They ought to be the same.
    See : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb08tra.htm#ADXDB4721

    This works well too :
     select l_xml.transform(l_xsl).getClobVal()
     into res
     from dual;
    What really matters here is the way we serialize the output when the text method is used.
    I called it a bug earlier, but when I look closely I think it makes sense after all :

    <li> When the text output method is specified, the serialization method (getClobVal or getStringVal) must be called in the same statement. I guess that's an implementation specificity so that, for example, character entities are output in their decoded form, and the content directly placed in a suitable variable.

    <li> If we serialized the output in a separate statement, then it's considered as XML again and since it contains unescaped entities (because of the text method), raises the error.
  • 6. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    tsuji Journeyer
    Currently Being Moderated
    [0] I would tend to attribute the oddity to the bug of the transform() method in the sense its support of the xsl element xsl:output is defective other than method="xml". In some circumstances, oddity can arise in regard to the attribute omit-xml-declaration as well - but that is another story.

    [0.1] The signature of the transform method suggests XMLType instance being involved, hence, it would be very odd indeed to demand text element in serialized xml need double escape to work. In fact, people would anticipate the cycle need no intervention of operation of double escape.
    xmltype instance -> serialized xml string ->xmltype instance (via xmltype() or xmltype.createxml())
    [1] As to how to make the op's original rendering work, it can be done with two amendments.
    [1.1] Instead of method="text", replace it by method="xml". This relates to the "bug", I would say.
    [1.2] In order to get the result which happens to be just a character string with unescaped character entities or xml built-in escape (&amp; or &gt; etc...) , apply the dbms_xmlgen.convert() should do.

    [2] This is the amended script closest to the op's original reasoning process.
    set define off;
    declare
    l_xsl xmltype;
    l_xml xmltype;
    begin
     
    l_xsl := xmltype('<?xml version="1.0"?>
    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml"/>
     
    <xsl:template match="/">
     
    Hello <xsl:value-of select="//name"/>
     
    </xsl:template>
    </xsl:stylesheet>');
     
    l_xml := xmltype('<test><name>Jane&amp;John Doe</name></test>');
     
    dbms_output.put_line(dbms_xmlgen.convert(l_xml.transform(l_xsl).getclobval(), 1));
    end;
    [2.1] In this approach, you can equally input a legitimate CDATA section instead without any change needed.
    l_xml := xmltype('<test><name><![CDATA[Jane&John Doe]]></name></test>');
    Edited by: tsuji on Sep 9, 2011 6:03 AM
  • 7. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    odie_63 Guru
    Currently Being Moderated
    [0.1] The signature of the transform method suggests XMLType instance being involved, hence, it would be very odd indeed to demand text element in serialized xml need double escape to work.
    I agree that demanding text output is counterintuitive, considering the function/method returns an XMLType.

    However, I believe Oracle's implementation of the text method is not that flawed, since it returns the correct result, with unescaped entities (if any).
    The only thing to know is that the serialization method must be applied straight after, in the same statement, so that no invalid XMLType exists.

    So rather than calling it a bug, maybe just another undocumented feature :)
  • 8. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    tsuji Journeyer
    Currently Being Moderated
    Hi odie, I would like to substantiante a bit more. If something I do not fall inline with what you try to say, most probably, I am not disagree with you, just say something different.

    [3] I agree that demanding text output is counterintuitive, considering the function/method returns an XMLType.
    I am not sure the sense for XMLType, but, it is very common in xslt to use method="text" other than the default method="xml" in the absence of it. It is used all the time. I substantiate more below.

    [4] Anything that requires double escape in one part of the an xml but not the other is suspect.
    [4.1] This is very suspect, if needed for specific processing in the xml-related technologies.
        <name>J&amp;amp;J</name>
    [4.2] This is fine as it double escapes everything as often appeared in payload or message transmission. (no hyphen artifact for forum-rendering.)
        &-lt;name/&-gt;J&amp;amp;J&-lt;name/&-gt;
    [5] If I do a quick check over other implementation, the outcomes can be summaried as follows.
    technolgies               xml           text        comment
    xerces/xalan              J&amp;J       J&J         good
    msxml2                    J&amp;J       J&J         good
    saxon                     J&amp;J       J&J         good
    oracle xmlparser.v2       J&amp;J       J&amp;J     text method output arguable
    oracle db>=10g            J&amp;J       ???         text method need double escape cdata part
    built-in xmlType
    I have had a hard time aligning! It sure is a very incomplete run-down, including mssql and many other implementations. But that is general idea.

    [5.1] If I make a quick page turning, in the <Oracle 10g Database - XML & SQL: Design, Build & Manage XML Applications in Java, C, C++ & PL/SQL> (2004), maybe a bit dated, no where in xslt related demo or illustration appears xsl:output element. It always use default xml implicitly. The support of text or html method is uncertain. In its p.479, there is an demo quite similar to the subject of this thread only using more sql of select keyword. In any case, to what extends oracle.xml.parser.v2 or xmlType() supports of text method in the xsl document is never clearly stated.
  • 9. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    odie_63 Guru
    Currently Being Moderated
    [4] Anything that requires double escape in one part of the an xml but not the other is suspect.
    Agreed, but see my example again (tested in 11.2.0.1), no double-escaping used, and the output is correct.

    The double-escape trick is just what OP found as a workaround for his code, but then it's equivalent to using method="xml" in the first place (as you showed a few post after).
  • 10. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    tsuji Journeyer
    Currently Being Moderated
    [6]
    +...but see my example again (tested in 11.2.0.1), no double-escaping used, and the output is correct.+
    That part is well noticed. And that precisely poses problem.

    [6.1] If I may make an analogy, using xmltype(xmlstring) or xmltype.createxml(xmlstring) is like msxml2's parser.loadxml(xmlstring) method of a parser or xerces's documentbuilder.parse(new InputSource(new StringReader(xmlstring))). It parses to dom tree from a serialized xml document. Whereas, with the intervention of xmlelement() etc, it is analogous to build the dom tree with the dom specific api like createElement(), setTextContent() or createTextNode() etc... The problem is to remain in the line of reasoning of building dom tree from a serialized xml document rather than first use a text parser to dissect the string to it functional parts and then build the dom tree. The latter is just like writing a simplified xml parser. You can do that because the op's problem is so much reduced and that the text parsing part is out-of-interest. I appreciate the excellence of that solution but that is not very practical in the sense xmlstring passing up is very real in practice.

    [6.2] But the working (at least for this sample) of the latter but not the former using xmltype() precisely pose the problem of either xmltype().transform() implementation contains bug or that more generally, oracle.xml.parser.v2 package has limited support of xslt xsl:output with method="text". As said, I tend to say it's a... bug. In any case, you know well there is a something fishy going on, one way of looking at it or another.

    Edited by: tsuji on Sep 11, 2011 6:31 AM

    Edited by: tsuji on Sep 11, 2011 6:53 AM
  • 11. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    odie_63 Guru
    Currently Being Moderated
    Hi tsuji,

    Interesting discussion, really, but I fail to see how the way we're generating the XMLType instance in the first place is important for any subsequent transformation.
    You said :
    That part is well noticed. And that precisely poses problem.
    and,
    [6.2] But the working (at least for this sample) of the latter but not the former using xmltype() precisely pose the problem of either xmltype().transform() implementation contains bug [...]
    Could you explain again what you think works and what doesn't?

    I may be missing something but all these appear to work correctly :
    SQL> var xsldoc varchar2(4000)
    SQL> begin
      2   :xsldoc := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      3  <xsl:output method="text"/>
      4  <xsl:template match="/">Hello <xsl:value-of select="//name"/>
      5  </xsl:template>
      6  </xsl:stylesheet>';
      7  end;
      8  /
     
    PL/SQL procedure successfully completed
     
    SQL> set define off
    SQL> select xmltransform(
      2           xmlelement("test", xmlelement("name", 'Jane&John Doe'))
      3         , :xsldoc
      4         ).getclobval() as result
      5  from dual
      6  ;
     
    RESULT
    --------------------------------------------------------------------------------
    Hello Jane&John Doe
     
    SQL> select xmltransform(
      2           xmltype('<test><name>Jane&amp;John Doe</name></test>')
      3         , :xsldoc
      4         ).getclobval() as result
      5  from dual
      6  ;
     
    RESULT
    --------------------------------------------------------------------------------
    Hello Jane&John Doe
     
    SQL> select xmltype(
      2           '<test><name>Jane&amp;John Doe</name></test>'
      3         ).transform(xmltype(:xsldoc)).getclobval() as result
      4  from dual
      5  ;
     
    RESULT
    --------------------------------------------------------------------------------
    Hello Jane&John Doe
     
    SQL> select xmlelement("test",
      2           xmlelement("name", 'Jane&John Doe')
      3         ).transform(xmltype(:xsldoc)).getclobval() as result
      4  from dual
      5  ;
     
    RESULT
    --------------------------------------------------------------------------------
    Hello Jane&John Doe
     
    In any case, you know well there is a something fishy going on, one way of looking at it or another.
    Yes, I agree. For sure, Oracle XSLT processors are not bug-free.
    For the case we're looking at now, the essence of the XMLType datatype is probably more to blame than the XSLT processor though.

    A little off-topic addendum about the method="html" output :
    SQL> select xmltransform(xmlelement("test",
      2           xmlelement("name", 'Jane&John Doe')
      3         ),'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      4  <xsl:output method="html" encoding="iso-8859-15" indent="yes"/>
      5  <xsl:template match="/"><html><head/><body><p><xsl:value-of select="//name"/></p></body></html>
      6  </xsl:template>
      7  </xsl:stylesheet>').getclobval()
      8  as result
      9  from dual;
     
    RESULT
    --------------------------------------------------------------------------------
     
    <html>
     <head>
      <meta http-equiv="Content-Type" content="text/html" charset="iso-8859-15">
      </meta>
     </head>
     <body>
      <p>Jane&#38;John Doe</p>
     </body>
    </html>
     
    Not perfect but the method seems supported too.

    Edited by: odie_63 on 11 sept. 2011 20:43
  • 12. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Annoying part is probably also that you don't know when or what. DB 11.2.0.2.0 (XE 11g eg.) doesn't need the JVM anymore so...different stuff in place and not something like oracle.xml.parser.v2

    Updated my http://www.liberidu.com/blog/?p=635 to keep a bit ontrack again regarding specific solutions per version.
  • 13. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    pl_sequel Newbie
    Currently Being Moderated
    Seems like I have opened Pandora's box with this one ;-)

    Thanks all for your input and expertise, much appreciated.

    I have gone with the following solution, based on your input:

    Set xsl:output="xml" on my "text-only" stylesheet, and call dbms_xmlgen.convert to decode the entities back to actual characters.

    FWIW, I'm using these xml transformations to generate email body content, for inclusing in multipart-mime type emails, which consist of "text-only" and html version of the email content. that is why I needed the entities decoded to their actual representation in my text version, otherwise the characters would end up being displayed as their encoded version in the text-only version of the email.
  • 14. Re: Escaped ampersand still causes LPX-00242 in xmltype.transform
    user640107 Newbie
    Currently Being Moderated
    I'm using Oracle 11g XE and I see the following:

    declare
    l_xsl xmltype;
    l_xml xmltype;
    res clob;
    begin
    l_xsl := xmltype('<?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
    <xsl:output method="text" omit-xml-declaration="yes"/>
    <xsl:template match="/">
    <xsl:value-of select="/Case/EmployeeDetails/FullName"/>
    <xsl:text> - </xsl:text>
    <xsl:value-of select="/Case/EmployeeDetails/IDnumber"/>
    </xsl:template>
    </xsl:stylesheet>');

    l_xml := xmltype('<Case><EmployeeDetails><FullName>Jane &amp; John Doe</FullName><IDnumber>123456</IDnumber></EmployeeDetails></Case>');

    select xmltransform(l_xml,l_xsl).*getclobval()* into res
    from dual;
    dbms_output.put_line(res);

    end;
    /

    works 100% and produces "Jane & John Doe - 123456"

    but if I change to the following:

    res varchar2(4000);
    and xmltransform(l_xml,l_xsl).*getstringval()* into res

    then I get

    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00242: invalid use of ampersand ('&') character (use &amp;)
    Error at line 1
    ORA-06512: at line 18


    but replacing "&amp;" with "and" works fine for both (ie. producing Jane and John Doe - 123456)

    ...so I'm not clear &amp; causes getstringval() to fail but not getclobval() . Any ideas welcome.

    Edited by: user640107 on 2013/04/26 6:54 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points