Home > XSLT > XMLTransform Tips and Tricks

XMLTransform Tips and Tricks

November 16, 2014 Leave a comment Go to comments

No real new stuff there but as I’m a big user of XSLT transformation inside the database, I just thought I could share a few interesting (hidden) things I’ve encountered so far, that is :

  1. Using XSLT parameters with XMLTransform
  2. Using text output method with XMLTransform and XMLType.transform method

 

1. Using XSLT parameters with XMLTransform

Though quite surprisingly still not officially documented, SQL function XMLTransform() does support passing XSLT top-level parameters to the stylesheet.
That is achieved via a third optional argument, just like its counterpart XMLType.transform method, and accepts the same syntax :
xslt_parammap

For example :

SQL> select xmlserialize(document
  2           xmltransform(
  3             xmlelement("dummy")
  4           , xmlparse(document
  5  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  6    <xsl:output method="xml"/>
  7    <xsl:param name="string-param"/>
  8    <xsl:param name="number-param"/>
  9    <xsl:template match="/">
 10      <result>
 11        <param1><xsl:value-of select="$string-param"/></param1>
 12        <param2><xsl:value-of select="$number-param"/></param2>
 13      </result>
 14    </xsl:template>
 15  </xsl:stylesheet>')
 16          , q'{string-param="'ABC'" number-param="123"}'
 17          )
 18          indent
 19        ) as output
 20  from dual ;

OUTPUT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<result>
  <param1>ABC</param1>
  <param2>123</param2>
</result>

 

2. Using text output method with XMLTransform and XMLType.transform method

Oracle supports XSLT text output method, via <xsl:output method=”text”/> directive, but since XMLTransform always returns an XMLType instance, it gets tricky and eventually errors out when the output contains reserved characters.

Here’s a simple example :

SQL> set define off
SQL> 
SQL> select xmltransform(
  2           xmlelement("input", 'R&D Department')
  3         , xmlparse(document
  4  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5    <xsl:output method="text"/>
  6    <xsl:template match="/">
  7     <xsl:text>Input text : </xsl:text>
  8     <xsl:value-of select="input"/>
  9    </xsl:template>
 10  </xsl:stylesheet>')
 11          ) as output
 12  from dual ;

ERROR:
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

The result of the transformation should be the following string :

Input text : R&D Department

But since the ampersand character doesn’t come in its escaped form, it fails to validate as a wellformed XMLType.

There are two solutions to this :

a) using a regular xml output method and cast back the XMLType result to a string, via XMLCast() function for instance

SQL> select xmltransform(
  2           xmlelement("input", 'R&D Department')
  3         , xmlparse(document
  4  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5    <xsl:output method="xml" omit-xml-declaration="yes"/>
  6    <xsl:template match="/">
  7     <xsl:text>Input text : </xsl:text>
  8     <xsl:value-of select="input"/>
  9    </xsl:template>
 10  </xsl:stylesheet>')
 11          ) as output
 12  from dual ;

OUTPUT
--------------------------------------------------------------------------------
Input text : R&amp;D Department

SQL> select xmlcast(
  2           xmltransform(
  3             xmlelement("input", 'R&D Department')
  4           , xmlparse(document
  5    '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  6      <xsl:output method="xml" omit-xml-declaration="yes"/>
  7      <xsl:template match="/">
  8       <xsl:text>Input text : </xsl:text>
  9       <xsl:value-of select="input"/>
 10      </xsl:template>
 11    </xsl:stylesheet>')
 12            )
 13            as varchar2(30)
 14          ) as output
 15  from dual ;

OUTPUT
------------------------------
Input text : R&D Department

 

b) using (deprecated) XMLType.getClobVal() method

When getClobVal method is applied directly on the result of XMLTransform (or XMLType.transform), Oracle rewrites the query using undocumented function XMLTransformBlob().
Contrary to what its name implies, this function actually returns a CLOB, and since there’s no intermediate XMLType object in the process, it returns the expected string result when combined with XSLT text output method :

SQL> select xmltransform(
  2           xmlelement("input", 'R&D Department')
  3         , xmlparse(document
  4  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  5    <xsl:output method="text"/>
  6    <xsl:template match="/">
  7     <xsl:text>Input text : </xsl:text>
  8     <xsl:value-of select="input"/>
  9    </xsl:template>
 10  </xsl:stylesheet>')
 11          ).getclobval()   as output
 12  from dual ;

OUTPUT
--------------------------------------------------------------------------------
Input text : R&D Department

Running a 10053 (CBO) trace on the query reveals the rewrite that occurred behind the scenes :

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT XMLTRANSFORMBLOB(XMLELEMENT("input",'R&D Department'),XMLPARSE( DOCUMENT '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>
  <xsl:template match="/">
   <xsl:text>Input text : </xsl:text>
   <xsl:value-of select="input"/>
  </xsl:template>
</xsl:stylesheet>')) "OUTPUT" FROM "SYS"."DUAL" "DUAL"

Note that this happens only when getClobVal is used, other serializing methods such as getStringVal or getBlobVal methods, or XMLSerialize function don’t trigger any rewrite.

Advertisements
  1. Michael M
    March 5, 2015 at 10:14

    Hi Odie – I would like to learn Oracle XML programming starting with Extracting node values from XML to INSERT,UPDATE,DELETE and many more. Would please recommend any books or links for beginners like me so that I could kick-start XML learning? Thanks for your help..

    Thanks
    Michael M

    • March 7, 2015 at 10:13

      Michael,
      The best place to start is the XML DB Dev Guide.
      If you have specific questions, you may ask over in the XML DB forum.

      • Michael M
        March 23, 2015 at 12:36

        Thanks Marc!!

  2. November 12, 2015 at 16:24

    Hi Odie,

    i want to do the following with an XML CLOB stream:
    1. Transform the XML CLOB stream via XMLTYPE and a stylsheet.
    2. Print the transformed XML on the IE 11 browser via htp.prn.

    Do you have an example which includes the appropriate stylsheet for IE 11?
    Many thanls in advance.

    Best Regards,
    Martin

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s