XSLT is the standard language for transforming XML: into another XML layout for a partner system, into HTML for a report, or into plain text. XMLTRANSFORM applies an XSLT stylesheet to an XML document inside the database, so data generated with SQL/XML can leave the database already in the shape the receiver needs.
Code for This Guide
The main example is in the examples/xml folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
xmltransform(xml_document, xsl_stylesheet)
Both arguments are XMLType values; the stylesheet is itself an XML document. The result is an XMLType holding the output, which XMLSERIALIZE turns into text.
Transform XML into Text
This stylesheet has output method text and a template that prints an airport's city followed by its code.
Example:
select xmlserialize(content
xmltransform(
xmltype('<airport code="DXB"><city>Dubai</city></airport>'),
xmltype('<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/airport">
<xsl:value-of select="city"/> (<xsl:value-of select="@code"/>)
</xsl:template>
</xsl:stylesheet>'))) as transformed
from dual;Output:
TRANSFORMED _________________________ Dubai (DXB)
Generate HTML from Table Data
The document can come from a query. Here XMLAGG builds a routes document for Singapore from the ROUTES table, and an XSLT template with xsl:for-each turns it into an HTML list.
Example:
-- XSLT that builds an HTML list from XML generated from a table
select xmlserialize(content
xmltransform(
(select xmlelement("routes", xmlagg(xmlelement("to", destination) order by destination))
from routes where origin = 'SIN'),
xmltype('<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/routes">
<ul><xsl:for-each select="to"><li><xsl:value-of select="."/></li></xsl:for-each></ul>
</xsl:template>
</xsl:stylesheet>'))) as html
from dual;Output:
HTML ________________ <ul> <li>DXB</li> <li>NRT</li> <li>SYD</li> </ul>
Things to Know
- Store stylesheets in a table with an XMLType column and select them into XMLTRANSFORM, so they can be changed without changing code.
- Oracle supports XSLT 1.0 stylesheets, which covers templates, xsl:for-each, xsl:if, xsl:choose, sorting, and variables.
- For simple reshaping, generating the target layout directly with XMLELEMENT and XMLAGG is often easier than a stylesheet.
Related Guides
Conclusion
XMLTRANSFORM applies an XSLT stylesheet to an XML document in SQL and returns the result as XML, HTML, or text. Combine it with SQL/XML generation to deliver data from tables in exactly the format another system expects.
