在Oracle SQL中使用未知名称空间解析XML [英] Parsing XML with unknown namespaces in Oracle SQL

问题描述

我在使用Oracle SQL和XML时遇到麻烦.

I'm having trouble with Oracle SQL and XMLs.

我将从外部系统获取大量格式良好的XML数据，以解析，解释和填充一些表.我使用XMLTable编写了一个解决方案，该解决方案布置在表的视图中，其中包含有问题的xml clob列以及一些审核信息和内容(我想保持这种方式).

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在给我做噩梦.显然我不能将它们放在xmlnamespaces子句中，因为我永远不知道它们将是什么.荒谬！相同类型的已交付项目在不同的时间点可能具有不同的命名空间.没有有限的清单.甚至默认的xmlns都不是常量.

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.

到目前为止，我想出的最好的解决方案是一组regexp_replace(准确地说是3个)，在解析之前会擦除所有名称空间.但是性能是一个巨大的问题.

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?

推荐答案

我知道它已经很老了，但是今天我发现了它，并且想起了我尝试处理命名空间XML时遇到的痛苦.我的解决方案是使用XSLT转换去除名称空间，并将其作为普通的旧XML处理.我用来执行此操作的函数是:

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;

这篇关于在Oracle SQL中使用未知名称空间解析XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！