xml - 如何在标签的 url 部分提取 Oracle XML

Question

我不太了解 XML，无法知道使用什么术语来提问。我将混淆我试图解码的 XML 片段，希望不会使问题变得无法回答。
我有一些 XML，我想获得“第一个要获取的字符串”和“要获取的第二个字符串”的值。如何编写 Oracle 查询来获取它们？

<itemTypes>
<itemTypesList>
    <itemType>400SVFD2</itemType>
    <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
    <errorIfNoValue>C1NO</errorIfNoValue>
    <valueType>U</valueType>
    <valueSource>C1BF</valueSource>
    <value/>
    <billFactor>400SVFD2</billFactor>
    <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
    <valueAlgorithm/>
</itemTypesList>

如果我编写一个 XML 查询并放入整个标签，如果其中一些字段发生变化，会发生什么情况？所以我认为我应该能够只指出“ora:itemType”和“ora:billFactor”，而基本上忽略其余部分，对吧？
这将返回空值:

with test_table(xmldata) as (
select 
q'[
<root>
    <itemTypes>
    <itemTypesList>
        <itemType>400SVFD2</itemType>
        <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
        <errorIfNoValue>C1NO</errorIfNoValue>
        <valueType>U</valueType>
        <valueSource>C1BF</valueSource>
        <value/>
        <billFactor>400SVFD2</billFactor>
        <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
        <valueAlgorithm/>
    </itemTypesList>
</itemTypes>
</root>
]'
from dual
)
select
  xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/ora:itemType/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res
from test_table
;

Answer 1

第一个变体(使用 xmlquery 和 xpath 过滤器) :

with test_table(xmldata) as (
select 
q'[
<root>
    <itemTypes>
    <itemTypesList>
        <itemType>400SVFD2</itemType>
        <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
        <errorIfNoValue>C1NO</errorIfNoValue>
        <valueType>U</valueType>
        <valueSource>C1BF</valueSource>
        <value/>
        <billFactor>400SVFD2</billFactor>
        <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
        <valueAlgorithm/>
    </itemTypesList>
</itemTypes>
</root>
]'
from dual
)
select 
  xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:itemType[namespace-uri()="http://www.oracle.com/something/somethingelse"]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res1
 ,xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:billFactor[namespace-uri()="http://www.oracle.com/something/somethingelse"]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res2
from test_table
/

结果:

RES1                           RES2
------------------------------ ------------------------------
First String to Get            Second String to Get

备注 您的元素在 xmlnamespace 'ora' 中，因此您需要指定它，但由于 xmlquery 没有 XMLNAMESPACES 参数，您有两种选择来指定它们:

在 xquery 的开头添加 xmlnamespace 声明:

'declare namespace ora = "http://www.oracle.com/something/somethingelse";...

或使用函数过滤元素 namespace-uri() :

*:itemType[namespace-uri()="http://www.oracle.com/something/somethingelse"]

*:element表示您需要 element来自任何 xml 命名空间。 [namespace-uri()="..."]按 namespace 过滤元素。
第二个变体:usint xmltable(xmlnamespaces(...)...)

with test_table(xmldata) as (
select 
q'[
<root>
    <itemTypes>
    <itemTypesList>
        <itemType>400SVFD2</itemType>
        <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
        <errorIfNoValue>C1NO</errorIfNoValue>
        <valueType>U</valueType>
        <valueSource>C1BF</valueSource>
        <value/>
        <billFactor>400SVFD2</billFactor>
        <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
        <valueAlgorithm/>
    </itemTypesList>
</itemTypes>
</root>
]'
from dual
)
select 
  xx.*
from test_table
    ,xmltable(
        xmlnamespaces('http://www.oracle.com/something/somethingelse' as "ORA", default ''),
        '/root/itemTypes/itemTypesList'
         passing xmltype(xmldata)
         columns
             res1   varchar2(100) path 'ORA:itemType/text()'
            ,res2   varchar2(100) path 'ORA:billFactor/text()'
    ) xx
;

结果:

RES1                           RES2
------------------------------ ------------------------------
First String to Get            Second String to Get

1 row selected.

还有一些基于通配符命名空间和过滤器的更短的变体:

select
  xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:itemType[namespace-uri()!=""]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res1
  ,xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:billFactor[2]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res2
from test_table

第一个返回带有非空命名空间 uri 的元素，第二个只返回第二个 billFactor 而不按命名空间过滤