时间戳

首页 > 解决方案 > 从 XMLTABLE 中提取数据的 Oracle 函数

xml - 从 XMLTABLE 中提取数据的 Oracle 函数

问题描述

我正在尝试在 Oracle 中编写一个函数，它允许我指定标签名称并返回一个特定的值。所以，如果下面的代码片段是我的 XML 的一部分，我想发送路径、序列号和我想要的特定字段，并取回值。

<variables>
<variablesList>
    <sequence>1</sequence>
    <variableType>C1SQ</variableType>
    <missingValueAction>C1ER</missingValueAction>
    <defaultValue>0</defaultValue>
    <uom>KWB</uom>
    <tou>SUMMER</tou>
    <serviceQuantityToUse>C1BI</serviceQuantityToUse>
    <targetCalcLines/>
</variablesList>
<variablesList>
    <sequence>2</sequence>
    <variableType>C1SQ</variableType>
    <missingValueAction>C1ER</missingValueAction>
    <defaultValue>0</defaultValue>
    <uom>KWB</uom>
    <tou>WINTER</tou>
    <serviceQuantityToUse>C1BI</serviceQuantityToUse>
    <targetCalcLines/>
</variablesList>

我的函数头定义如下：

CREATE OR REPLACE function cm_get_bo_data_TableValue (pTableTag varchar2, pFieldTag varchar2, pSequence varchar2, pBODataValue CLOB)

我这样调用函数：

select BO_DATA_AREA
 , cm_get_bo_data_TableValue ('/root/variables/variablesList', 'tou', '2', BO_DATA_AREA)
from CM_CI1558A_BO_DATA_AUDIT

但是，它只适用于“tou”，因为我似乎无法在 PATH 中放置变量。

因此，如果我有以下内容，它将返回一个值：

select TagValue into v_value
from dual, XMLTable(pTableTag PASSING XMLTYPE( to_clob('<root>') || pBODataValue || '</root>' ) 
                  COLUMNS sequence_num          VARCHAR2(10)    PATH 'sequence',
                          TagValue              VARCHAR2(10)    PATH 'tou'
    
) t
where sequence_num = pSequence;

return v_value;

但是如果我用 pFieldTag 替换那个 'tou'，它会说 SQL 是错误的，并告诉我它需要一个字符串。

                              TagValue              VARCHAR2(10)    PATH pFieldTag

我希望能够指定每个特定标签并取回值；我需要一次处理一个，并且想要一些通用的东西，而不是这种特定的 XML 结构。

换句话说，我希望能够为 tou 指定序列 1 并获得“SUMMER”，为 uom 指定序列 2 并获得“KWB”，为完全不同的标签和结构指定序列 1 并获得该值 - 对于任何我们可能遇到的表值（有几个）。

标签： xmloraclexmltable

解决方案

你甚至不需要为此创建特殊的函数，因为 Oracle 有非常好的函数 xmlquery。例如，如果您想通过 xquery 从 xmltype 中获取一些值：

xmlcast( xmlquery( '/root/variables/variablesList[2]/tou' -- <== 你的路径，从你的问题传递 xmltype(xmldata) 返回内容 ) 作为 varchar2(100) ) res

完整示例：

with test_table(xmldata) as (
select 
q'[
<variables>
<variablesList>
    <sequence>1</sequence>
    <variableType>C1SQ</variableType>
    <missingValueAction>C1ER</missingValueAction>
    <defaultValue>0</defaultValue>
    <uom>KWB</uom>
    <tou>SUMMER</tou>
    <serviceQuantityToUse>C1BI</serviceQuantityToUse>
    <targetCalcLines/>
</variablesList>
<variablesList>
    <sequence>2</sequence>
    <variableType>C1SQ</variableType>
    <missingValueAction>C1ER</missingValueAction>
    <defaultValue>0</defaultValue>
    <uom>KWB</uom>
    <tou>WINTER</tou>
    <serviceQuantityToUse>C1BI</serviceQuantityToUse>
    <targetCalcLines/>
</variablesList>
</variables>
]'
from dual
)
select
  xmlcast(
     xmlquery(
        '&input_path[&input_sequence]/&input_tag/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res
from test_table
/

正如您在此示例中所看到的，我正在按照您的要求从替换变量构建路径，即“path[sequence]/tag”。所以你需要输入：

input_path: /variables/variablesList
input_sequence: 2
input_tag: tou

PS。在这个例子中我没有添加标签，所以我没有在开头指定/root（你为什么需要它？你没有有效的xml（没有父标签）作为输入吗？）

推荐阅读