ORA-20000

In a previous post, I showed you how to create your own transformer xslt, use the Oracle XDK.

Consider the new features that give us their own transformer.

All features can be read here

Now Let's consider "Temporary trees".

In the first version of xslt, you can use the built-in extension must be used ora:node-set.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ora="http://www.oracle.com/XSL/Transform/java"  
                version="1.0">

  <xsl:output method="xml" indent="yes"/>
  
  <xsl:variable name="v-genre-type">
    <genre>
      <id>1</id>
      <name>Computer</name>
    </genre>
    <genre>
      <id>2</id>
      <name>Fantasy</name>
    </genre>
    <genre>
      <id>3</id>
      <name>Romance</name>
    </genre>
    <genre>
      <id>4</id>
      <name>Horror</name>
    </genre>
    <genre>
      <id>5</id>
      <name>Science Fiction</name>
    </genre>
  </xsl:variable>  

  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  
  <xsl:template match="genre">
    <xsl:variable name="v-genre-value"><xsl:value-of select="."/></xsl:variable>
    <xsl:element name="{local-name()}"><xsl:value-of select="ora:node-set($v-genre-type)/genre[name=$v-genre-value]/id"/></xsl:element>
  </xsl:template>

  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  
</xsl:stylesheet>~'                                                        
        )
from   xml_sample;

Result:

<?xml version="1.0" encoding="UTF-8"?>
<catalog>
 <book id="bk101">
  <author>Gambardella, Matthew</author>
  <title>XML Developer's Guide</title>
  <genre>1</genre>
  <price>44.95</price>
  <publish_date>2000-10-01</publish_date>
  <description>An in-depth look at creating applications
  with XML.</description>
 </book>
 <book id="bk102">
  <author>Ralls, Kim</author>
  <title>Midnight Rain</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2000-12-16</publish_date>
  <description>A former architect battles corporate zombies,
  an evil sorceress, and her own childhood to become queen
  of the world.</description>
 </book>
 <book id="bk103">
  <author>Corets, Eva</author>
  <title>Maeve Ascendant</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2000-11-17</publish_date>
  <description>After the collapse of a nanotechnology
  society in England, the young survivors lay the
  foundation for a new society.</description>
 </book>
 <book id="bk104">
  <author>Corets, Eva</author>
  <title>Oberon's Legacy</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2001-03-10</publish_date>
  <description>In post-apocalypse England, the mysterious
  agent known only as Oberon helps to create a new life
  for the inhabitants of London. Sequel to Maeve
  Ascendant.</description>
 </book>
 <book id="bk105">
  <author>Corets, Eva</author>
  <title>The Sundered Grail</title>
  <genre>2</genre>
  <price>5.95</price>
  <publish_date>2001-09-10</publish_date>
  <description>The two daughters of Maeve, half-sisters,
  battle one another for control of England. Sequel to
  Oberon's Legacy.</description>
 </book>
 <book id="bk106">
  <author>Randall, Cynthia</author>
  <title>Lover Birds</title>
  <genre>3</genre>
  <price>4.95</price>
  <publish_date>2000-09-02</publish_date>
  <description>When Carla meets Paul at an ornithology
  conference, tempers fly as feathers get ruffled.</description>
 </book>
 <book id="bk107">
  <author>Thurman, Paula</author>
  <title>Splish Splash</title>
  <genre>3</genre>
  <price>4.95</price>
  <publish_date>2000-11-02</publish_date>
  <description>A deep sea diver finds true love twenty
  thousand leagues beneath the sea.</description>
 </book>
 <book id="bk108">
  <author>Knorr, Stefan</author>
  <title>Creepy Crawlies</title>
  <genre>4</genre>
  <price>4.95</price>
  <publish_date>2000-12-06</publish_date>
  <description>An anthology of horror stories about roaches,
  centipedes, scorpions  and other insects.</description>
 </book>
 <book id="bk109">
  <author>Kress, Peter</author>
  <title>Paradox Lost</title>
  <genre>5</genre>
  <price>6.95</price>
  <publish_date>2000-11-02</publish_date>
  <description>After an inadvertant trip through a Heisenberg
  Uncertainty Device, James Salway discovers the problems
  of being quantum.</description>
 </book>
 <book id="bk110">
  <author>O'Brien, Tim</author>
  <title>Microsoft .NET: The Programming Bible</title>
  <genre>1</genre>
  <price>36.95</price>
  <publish_date>2000-12-09</publish_date>
  <description>Microsoft's .NET initiative is explored in
  detail in this deep programmer's reference.</description>
 </book>
 <book id="bk111">
  <author>O'Brien, Tim</author>
  <title>MSXML3: A Comprehensive Guide</title>
  <genre>1</genre>
  <price>36.95</price>
  <publish_date>2000-12-01</publish_date>
  <description>The Microsoft MSXML3 parser is covered in
  detail, with attention to XML DOM interfaces, XSLT processing,
  SAX and more.</description>
 </book>
 <book id="bk112">
  <author>Galos, Mike</author>
  <title>Visual Studio 7: A Comprehensive Guide</title>
  <genre>1</genre>
  <price>49.95</price>
  <publish_date>2001-04-16</publish_date>
  <description>Microsoft Visual Studio 7 is explored in depth,
  looking at how Visual Basic, Visual C++, C#, and ASP+ are
  integrated into a comprehensive development
  environment.</description>
 </book>
</catalog>

The second version of xslt, Temporary trees are automatically converted into node-set.

select ora_20000_xmlutility.XMLTransformClob
        (xml_sample.xmltext,
q'~<?xml version="1.0" encoding="Windows-1251"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ora="http://www.oracle.com/XSL/Transform/java"  
                version="2.0">

  <xsl:output method="xml" indent="yes"/>
  
  <xsl:variable name="v-genre-type">
    <genre>
      <id>1</id>
      <name>Computer</name>
    </genre>
    <genre>
      <id>2</id>
      <name>Fantasy</name>
    </genre>
    <genre>
      <id>3</id>
      <name>Romance</name>
    </genre>
    <genre>
      <id>4</id>
      <name>Horror</name>
    </genre>
    <genre>
      <id>5</id>
      <name>Science Fiction</name>
    </genre>
  </xsl:variable>  

  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="node()|@*" />
    </xsl:element>
  </xsl:template>
  
  <xsl:template match="genre">
    <xsl:variable name="v-genre-value"><xsl:value-of select="."/></xsl:variable>
    <xsl:element name="{local-name()}"><xsl:value-of select="$v-genre-type/genre[name=$v-genre-value]/id"/></xsl:element>
  </xsl:template>

  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>
  </xsl:template>
  
</xsl:stylesheet>~'                                                        
        )
from   xml_sample;

Additional links:

• Custom XSLT with Oracle XDK library in ORACLE DB
• Custom XSLT with JAPX library in ORACLE DB
• XML Transformation with XSLT 1.0 and 2.0