使用Oracle PL/SQL从具有名称空间的xml Clob中提取值 [英] Extracting value from xml clob with Namespace using Oracle pl/sql

问题描述

我目前正在尝试从clob列中提取值，但始终将结果作为null.

I am currently trying to extract value from the clob column but always getting the result as null.

我尝试了许多可能的情况，但对我来说，它总是以null的形式返回.

I tried out many possible scenarios but for me it is always returning as null.

附带的是实际的xml

Attached is the actual xml

<TenderOffer xmlns="http://xmlns.oracle.com/apps/otm">
    <Shipment>
        <ShipmentHeader/>
        <SEquipment/>
        <ShipmentStop/>
        <ShipmentStop/>
        <Location/>
        <Location/>
        <Release/>
        <RATE_OFFERING>
            <RATE_OFFERING_ROW>
                <USER_CLASSIFICATION3>ZXF</USER_CLASSIFICATION3>
            </RATE_OFFERING_ROW>
        </RATE_OFFERING>
    </Shipment>
    </TenderOffer>

和下面是实际查询，

select 
        itc.element_name,
        extractvalue(XMLTYPE(XML_BLOB), '/TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW/USER_CLASSIFICATION3/text()'),
    XMLTYPE(XML_BLOB)
        from i_transaction itc
        where itc.i_transaction_no = 31553115
            and rownum = 1

推荐答案

您更新的XML具有名称空间，该名称空间最终揭示了问题所在.您需要指定名称空间作为XML提取的一部分，使用XMLTable方法更简单；在这种情况下，您可以将其视为默认名称空间:

Your updated XML has a namespace, which finally reveals the issue. You need to specify the namespace as part of the XML extraction, which is simpler with the XMLTable approach; in this case you can just treat it as the default namespace:

select itc.element_name, x.user_classification3
from i_transaction itc
cross join xmltable(
  xmlnamespaces(default 'http://xmlns.oracle.com/apps/otm'),
    '/TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW'
  passing xmltype(itc.xml_blob)
  columns user_classification3 varchar2(10) path 'USER_CLASSIFICATION3'
) x
where itc.i_transaction_no = 31553115
and rownum = 1;

ELEMENT_NA USER_CLASS
---------- ----------
dummy      ZXF       

或使用XMLQuery:

or with XMLQuery:

select itc.element_name, xmlquery(
  'declare default element namespace "http://xmlns.oracle.com/apps/otm"; (: :)
    /TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW/USER_CLASSIFICATION3/text()'
  passing xmltype(itc.xml_blob)
  returning content
) x
from i_transaction itc
where itc.i_transaction_no = 31553115
and rownum = 1;

ELEMENT_NA X                                                                               
---------- --------------------------------------------------------------------------------
dummy      ZXF                                                                             

如果您想继续使用不推荐使用的extractvalue()函数，则也可以提供名称空间作为其参数，再次

If you wanted to keep using the deprecated extractvalue() function you can supply the namespace as an argument to that too, again as shown in the documentation:

select itc.element_name,
  extractvalue(xmltype(xml_blob),
    '/TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW/USER_CLASSIFICATION3/text()',
    'xmlns="http://xmlns.oracle.com/apps/otm"')
from i_transaction itc where itc.i_transaction_no = 31553115 and rownum = 1;

这篇关于使用Oracle PL/SQL从具有名称空间的xml Clob中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！