Transformation on XMLType in Oracle XML DB

When using a XMLType table/column in Oracle XML DB to store structured data you are able to perform transformations with the XMLTRANSFORM command. We have a requirement to remove all namespaces for the inserted XML content and decided to implement this mechanism in the database.

Probably piece of cake for a Oracle XML DB guru, I needed Google 😉

CREATE TABLE XMLSTYLESHEETS
(
CODE VARCHAR2(6 CHAR) NOT NULL,
STYLESHEET "XMLTYPE" NOT NULL,
);

We insert the XSLT stylesheet for removing namespaces in the table:

INSERT INTO XMLSTYLESHEETS (CODE, STYLESHEET)
values ('REMNAM' ,'
<xsl:stylesheet version="1.0" xmlns:xsl=http://www.w3.org/1999/XSL/Transform>
<xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@* | node()"/>
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="."/>
</xsl:attribute>
</xsl:template>
<xsl:template match="comment() | text() | processing-instruction()">
<xsl:copy></xsl:copy>
</xsl:template>
</xsl:stylesheet>');

Then we create a trigger to

create or replace
trigger MYTRIGGER
BEFORE INSERT OR UPDATE ON MYTABLEWITHXML
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
-- remove namespaces from XMLTYPE input
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
SELECT XMLTRANSFORM(:new.data_xml, x.stylesheet)
INTO :new.data_xml
FROM XMLSTYLESHEETS x
WHERE x.CODE = 'REMNAM';
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
END;

Let’s test the trigger:

Insert into MYTABLEWITHXML(MYDATAXML)
values ('
<bk:book xmlns:bk="urn:loc.gov:books" xmlns:isbn="urn:ISBN:0-395-36341-6">
<bk:title>Cheaper by the Dozen</bk:title>
<isbn:number>1568491379</isbn:number>
</bk:book>');

When enabling the DBMS_OUTPUT lines we are able to validate the result of the trigger.