xml - 具有多个子节点的 Oracle XML 查询

Question

我想实现对 XML 元素的查询，但找不到实现此目的的合适方法。

让我们考虑一下这个表:

CREATE TABLE SUBSCRIPTIONS(
SUBSCRIPTION_ID NUMBER,
PARAMETERS_XML  CLOB
);

我的 XML 看起来像:

<Model>
  <Parameters>
    <Parameter>
      <userParameter>outlets</userParameter>
       <Values>
         <Value>45</Value>
         <Value>676</Value>
         <Value>1502</Value>
         ...
       </Values>
    </Parameter>
  </Parameters>
</Model>

我正在尝试使用以下查询从 XML、参数名称和参数值中获取每个订阅:

SELECT
s.SUBSCRIPTION_ID,
s.PARAMETERS_XML,
xt.*
FROM EFNTA.SUBSCRIPTIONS s,
XMLTABLE(
    '/Model/Parameters/Parameter'
    PASSING XMLTYPE(s.PARAMETERS_XML)
    COLUMNS USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
    PARAM_VALUE VARCHAR2(1000) PATH '//*:Values',
    CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//*:CheckedListExclude'                        
) xt ORDER BY 1 DESC;

问题是当有多个值时，结果作为一个大的串联链返回:

有什么简单的方法可以用逗号、分号或其他任何方式分隔这些值吗？ (我事先不知道值的数量)我找不到如何使用 XPath 实现这一点。

Answer 1

您可以使用 FLOWR 将 XML 重写为包含尾随 ,在每个Value元素:

SELECT s.SUBSCRIPTION_ID,
       s.PARAMETERS_XML,
       xt.User_Param,
       RTRIM(xt.Param_Value, ',') AS Param_Value,
       xt.Checked_List_Exclude
FROM   EFNTA.SUBSCRIPTIONS s
       CROSS JOIN
       XMLTABLE(
         'copy $e := .
         modify (
           for $i in $e/Model/Parameters/Parameter/Values/Value
           return replace node $i with <Value>{$i},</Value>
         )
         return $e/Model/Parameters/Parameter'
         PASSING XMLTYPE(s.PARAMETERS_XML)
         COLUMNS
           USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
           PARAM_VALUE VARCHAR2(1000) PATH '//*:Values',
           CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//*:CheckedListExclude'
        ) xt
ORDER BY 1 DESC;

或者，按照 Pavel Koryakin 的建议, 使用 string-join() :

SELECT s.SUBSCRIPTION_ID,
       s.PARAMETERS_XML,
       xt.*
FROM   EFNTA.SUBSCRIPTIONS s
       CROSS JOIN
       XMLTABLE(
         '/Model/Parameters/Parameter'
         PASSING XMLTYPE(s.PARAMETERS_XML)
         COLUMNS
           USER_PARAM  VARCHAR2(100)  PATH 'userParameter',
           PARAM_VALUE VARCHAR2(1000) PATH 'Values/string-join(./Value/text(), ",")',
           CHECKED_LIST_EXCLUDE VARCHAR2(100) PATH '//CheckedListExclude'
        ) xt
ORDER BY 1 DESC;

这两个输出:

<表类="s-表"><头>SUBSCRIPTION_IDPARAMETERS_XMLUSER_PARAMPARAM_VALUECHECKED_LIST_EXCLUDE<正文>1

outlets

45
676
1502



/> 奥特莱斯45,676,1502 空

db<> fiddle here