XML parsing with unknown namespaces in Oracle SQL

Question

I'm having problems with Oracle SQL and XML.

I get a lot of data from well-formed XML data from an external system for parsing, interpreting and populating some tables. I wrote a solution using XMLTable, which is outlined in a table view with the corresponding xml clob column and some audit information, etc. (I would like to save it that way).

NAMESPACES give me nightmares. Apparently, I cannot put them in the xmlnamespaces clause, because I can never know what they will be. Absurdity! Delivered items of the same type may have different namespaces at different points in time. There is no end list. Even standard xmlns is not persistent.

The best working solution I came up with is the regexp_replace set (3, to be precise), erasing all namespaces before parsing. But performance is a huge problem.

Surely there is something smart missing?

Answer 1

I know this is pretty old, but I noticed it today and remembered the pain I experienced trying to deal with XML with names. My solution was to split the namespaces with the XSLT transformation and treat it like plain old XML. The function I used is:

function remove_namespace( i_xml in xmltype ) return xmltype is v_xml xmltype default i_xml; v_xsl varchar2(32767); begin v_xsl := '<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/> <xsl:template match="*"> <!-- remove element prefix (if any) --> <xsl:element name="{local-name()}"> <!-- process attributes --> <xsl:for-each select="@*"> <!-- remove attribute prefix (if any) --> <!-- this if filters out any xmlns="" atts that have no namespace prefix in the xml --> <xsl:if test="(local-name() != ''xmlns'')"> <xsl:attribute name="{local-name()}"> <xsl:value-of select="."/> </xsl:attribute> </xsl:if> </xsl:for-each> <xsl:apply-templates/> </xsl:element> </xsl:template> </xsl:stylesheet>'; return v_xml.transform(xmltype(v_xsl)); end;