oraclefrontovik

Sharing what I learn

In this article I will demonstrate several different methods you can use to perform XSLT from within PL/SQL.

The examples were built using Oracle Database 11.2.0.1.0 and SQL Developer 3.2

The examples are based on this XML document….

<?xml version="1.0"?>
<ROWSET>
 <ROW>
 <EMPNO>7566</EMPNO>
 <ENAME>JONES</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>02-APR-1981</HIREDATE>
 <SAL>2975</SAL>
 <DEPTNO>20</DEPTNO>
 <VALID>N</VALID>
 </ROW>
 <ROW>
 <EMPNO>7788</EMPNO>
 <ENAME>SCOTT</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>19-APR-1987</HIREDATE>
 <SAL>3000</SAL>
 <DEPTNO>20</DEPTNO>
 <VALID>N</VALID>
 </ROW>
</ROWSET>

and this XSLT document….

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
 <html>
 <body>
 <xsl:for-each select="ROWSET/ROW">
 <h2><xsl:value-of select="ENAME"/></h2>
 </xsl:for-each>
 </body>
 </html>
 </xsl:template>
</xsl:stylesheet>

XMLTRANSFORM

OK, so the first example is actually SQL rather than PL/SQL! XMTRANSFORM is a SQL Function that you can call from PL/SQL.  It accepts two arguments, both of which need to be XMLTYPE, one being the XML document that you want to transform and the other is the XSLT document.

Here is an example of it using the XML and XSLT files above:

DECLARE

l_xml XMLTYPE;
l_xsl XMLTYPE;
l_transformed XMLTYPE;

BEGIN

   l_xml := XMLTYPE.CREATEXML('<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>');

   l_xsl := XMLTYPE.CREATEXML('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>');

   SELECT XMLTRANSFORM(l_xml, l_xsl)
   INTO l_transformed
   FROM dual;

   DBMS_OUTPUT.PUT_LINE(l_transformed.getstringval());

END;
/
  • lines 3 – 5 I declare three XMLTYPE variables.
  • line 9 using the CREATEXML method I create a valid XML Document containing the source XML document and assign it to the l_xml variable
  • line 11 again using CREATEXML I create a valid XML document containing the XSLT stylesheet and assign it to the variable l_xsl
  • lines 13 – 15 I perform the XSLT transformation using XMLTRANSFORM. The result of which is placed in the variable l_transformed.
  • line 17 I output the contents of l_transformed using the getstringval method.

When the anonymous block is run you will see that the XML document has been transformed into HTML and only includes the values from ENAME

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

Transform member function of the XMLTYPE

The Oracle XMLTYPE has a member function that you can use for transforming your XML documents. Here is an example of it being used:

DECLARE

l_xml XMLTYPE;
l_xsl XMLTYPE;
l_transformed XMLTYPE;

BEGIN

 l_xml := XMLTYPE.CREATEXML('<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>');

 l_xsl := XMLTYPE.CREATEXML('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>');

 l_transformed := l_xml.transform(xsl => l_xsl);

 DBMS_OUTPUT.PUT_LINE(l_transformed.getstringval());

END;
/
  •  Lines 1 – 11, there is no difference from the example used to demostrate XMLTRANSFORM
  • At line 13 I call the TRANSFORM function of l_xml passing it the variable containing the XSLT document.

The output is shown below:

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

DBMS_XSLPROCESSOR

Given the relative ease of use of the previous two methods, using DBMS_XSLPROCESSOR requires a lot more code to transform an XML document. One advantage (the only?) is that this method doesn’t require the use of XMLTYPE.

The example below is taken from the Oracle documentation and modified to use the XML and XSLT documents that have been used throughout this post.


DECLARE

l_xml                     VARCHAR2(4000);
l_xsl                     VARCHAR2(4000);
l_parser                  dbms_xmlparser.parser;
l_xml_dom_document        dbms_xmldom.domdocument;
l_xslt_dom_document       dbms_xmldom.domdocument;
l_xslprocessor_ss_type    dbms_xslprocessor.stylesheet;
l_dom_doc_fragment        dbms_xmldom.domdocumentfragment;
l_dom_node                dbms_xmldom.domnode;
l_xsl_processor           dbms_xslprocessor.processor;
l_buffer                  VARCHAR2(2000);

BEGIN

 l_xml :='<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>';

 l_xsl := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>';

 l_parser := dbms_xmlparser.newparser;

 dbms_xmlparser.parsebuffer(l_parser, l_xml);

 l_xml_dom_document := dbms_xmlparser.getdocument(l_parser);

 dbms_xmlparser.parsebuffer(l_parser, l_xsl);

 l_xslt_dom_document := dbms_xmlparser.getDocument(l_parser);

 l_xslprocessor_ss_type := dbms_xslprocessor.newStyleSheet(l_xslt_dom_document, '');

 l_xsl_processor := dbms_xslprocessor.newprocessor;

 l_dom_doc_fragment := dbms_xslprocessor.processXSL(l_xsl_processor, l_xslprocessor_ss_type, l_xml_dom_document);

 l_dom_node := dbms_xmldom.makeNode(l_dom_doc_fragment);

 dbms_xmldom.writeToBuffer(l_dom_node, l_buffer);

 dbms_output.put_line(l_buffer);

 dbms_xmldom.freedocument(l_xml_dom_document);
 dbms_xmldom.freedocument(l_xslt_dom_document);
 dbms_xmldom.freedocfrag(l_dom_doc_fragment);
 dbms_xmlparser.freeparser(l_parser);
 dbms_xslprocessor.freeprocessor(l_xsl_processor);

END;
/

If you run this code you will see the expected output of:

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

Summary

This post has shown several different methods that you can use to transform your XML documents using XSLT from within PL/SQL.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: