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.