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.
iancarpenter.dev 