从具有多行数据的行值生成XML数据

Question

我需要一个SQL帮助来生成XML文件。

我有一个表，它有一列带有多行数据。

表名称：表1

列名：col1

1行数据如下：

VALUE1
VALUE2
VALUE3
VALUE4

我需要生成以下XML文件。

<col1>
<value>VALUE1</value>
<value>VALUE2</value>
<value>.</value>
<value>VALUE3</value>
<value>VALUE4</value>
</col1>

我已经使用下面的查询来格式化行数据。

select 
level,
case 
when regexp_substr(a.col_trimmed_value, '[^' || CHR(10) || ']+',  1, level) is not null
THEN substr(regexp_substr(a.col_trimmed_value, '[^' || CHR(10) || ']+',  1, level), 1, 200)
ELSE '.'
END as ROW_VALUE
from (
select
col1              as col_original_value
,replace(rtrim(ltrim(replace(col1,CHR(10),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#',CHR(10)) as col_trimmed_value
from
table1
)a
connect by level <=  length ( a.col_trimmed_value ) - length(replace( a.col_trimmed_value, CHR(10)) ) + 1 
;

预期结果：

LEVEL   ROW_VALUE
==================
1   VALUE1
2   VALUE2
3   .
4   VALUE3
5   VALUE4

实际结果：

LEVEL   ROW_VALUE
==================
1   VALUE1
2   VALUE2
3   VALUE3
4   VALUE4
5   .

有人能帮我解决这个问题吗？

提前谢谢。

Answer 1

您可以使用处理空元素的正则表达式模式：

select 
level,
case 
when regexp_substr(a.col_trimmed_value, '(.*?)(' || CHR(10) || '|$)', 1, level, null, 1) is not null
THEN substr(regexp_substr(a.col_trimmed_value, '(.*?)(' || CHR(10) || '|$)', 1, level, null, 1), 1, 200)
ELSE '.'
END as ROW_VALUE
from (
...

您也可以将其简化一点，以便只应用正则表达式一次：

select 
level,
coalesce(
substr(regexp_substr(a.col_trimmed_value, '(.*?)(' || CHR(10) || '|$)', 1, level, null, 1), 1, 200),
'.')
as ROW_VALUE
from (
...

ROW_VALUE1VALUE1值234VALUE3值4