Parsing XML with unknown namespaces in Oracle SQL

Question

I'm having trouble with Oracle SQL and XMLs.

I'll be getting loads of clobs of well-formed XML data from an external system to parse, interpret and fill some tables with. I wrote a solution using XMLTable, which is laid out in a view on the table with the xml clob column in question and some audit info and stuff (I'd like to keep it this way).

NAMESPACES are giving me nightmares. Apparently I can't put them in the xmlnamespaces clause, because I can never know what they are going to be. Preposterous! Delivered items of the same type could have different namespaces at different points in time. There's no finite list. Not even the default xmlns is constant.

The best working solution I've come up with so far is a set of regexp_replace (3, to be precise), erasing all the namespaces before parsing. But performance is a colossal issue.

Surely there's something clever I'm missing?

Nofretete

Do you have your code so that we can better assist you? – Obstinacy

Oracle enforces security and stops any calls to external XML schema URLs, this means all valid schemas needs to be registered within the database using DBMS_XMLSCHEMA(register schema), a lot of schemas are already registered in the DB. However if they are not then you have to get a list of them from all third parties (sending XMLs) and register them in Oracle. – Busoni

Answer 1

I know this is pretty old, but I spotted it today and remembered the pain I experienced trying to deal with namespaced XML. My solution was to strip out the namespaces with an XSLT transform and process it as plain old XML. The function I used to do this 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;

Titicaca