在 Oracle 查询中连接 XMLType 节点 [英] Concatenate XMLType nodes in Oracle query

问题描述

我有一个包含 XML 类型数据的 CLOB 列.例如 XML 数据是:

I have a CLOB column that contains XML type data. For example XML data is:

<A><B>123</b><C>456</C><B>789</b></A>

我已经尝试了 concat 函数:

I have tried the concat function:

concat(xmltype (a.xml).EXTRACT ('//B/text()').getStringVal (),';'))

或

xmltype (a.xml).EXTRACT (concat('//B/text()',';').getStringVal ()))

但是他们给了;"仅在末尾而不是在每个 标签之后.

But they are giving ";" at end only not after each <B> tag.

我正在使用

xmltype (a.xml).EXTRACT ('//B/text()').getStringVal () 

我想将所有 与 ; 连接起来，预期结果应该是 123;789

I want to concatenate all <B> with ; and expected result should be 123;789

请建议我如何连接我的数据.

Please suggest me how can I concatenate my data.

推荐答案

concat() SQL 函数连接两个值，因此它只是将分号独立地附加到每个提取的值.但您实际上是在尝试对结果进行字符串聚合(这可能真的是两个以上提取的值).

The concat() SQL function concatenates two values, so it's just appending the semicolon to each extracted value independently. But you're really trying to do string aggregation of the results (which could, presumably, really be more than two extracted values).

您可以使用 XMLQuery 代替提取，并使用 XPath string-join() 函数进行连接:

You can use XMLQuery instead of extract, and use an XPath string-join() function to do the concatentation:

XMLQuery('string-join(/A/B, ";")' passing xmltype(a.xml) returning content)

带有固定 XMl 端节点标签的演示:

Demo with fixed XMl end-node tags:

-- CTE for sample data
with a (xml) as (
  select '<A><B>123</B><C>456</C><B>789</B></A>' from dual
)
-- actual query
select XMLQuery('string-join(/A/B, ";")' passing xmltype(a.xml) returning content) as result
from a;

RESULT
------------------------------
123;789

您还可以使用 XMLTable 提取所有单独的 值，然后使用 SQL 级聚合:

You could also extract all of the individual <B> values using XMLTable, and then use SQL-level aggregation:

-- CTE for sample data
with a (xml) as (
  select '<A><B>123</B><C>456</C><B>789</B></A>' from dual
)
-- actual query
select listagg(x.b, ';') within group (order by null) as result
from a
cross join XMLTable('/A/B' passing xmltype(a.xml) columns b number path '.') x;

RESULT
------------------------------
123;789

这为您提供了更大的灵活性，并允许更轻松地按其他节点值进行分组，但根据您的示例值，这里似乎不需要这样做.

which gives you more flexibility and would allow grouping by other node values more easily, but that doesn't seem to be needed here based on your example value.

这篇关于在 Oracle 查询中连接 XMLType 节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！