Oracle中按fieldId统计XML多同名quickFilterValues节点数量

问题描述 #

存储在表中的XML文件结构特殊：quickFilterValues节点可能不存在、存在一次或多次。需要生成包含fieldId和quickFilterValues计数两列的报表，现有SQL仅能统计该节点总数量，无法按fieldId分组统计。

示例XML #

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataView>
    <fieldOptions>
        <fieldId>1</fieldId>
    </fieldOptions>
    <fieldOptions>
        <fieldId>2</fieldId>
        <quickFilterValues>100</quickFilterValues>
    </fieldOptions>
    <fieldOptions>
        <fieldId>3</fieldId>
        <quickFilterValues>10</quickFilterValues>
        <quickFilterValues>20</quickFilterValues>
        <quickFilterValues>30</quickFilterValues>
    </fieldOptions>
</dataView>

期望结果 #

尝试的SQL（无法分组统计） #

create table t(c clob);

insert into t values('<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>');

select *
from t
cross apply xmltable(
'/dataView/fieldOptions/quickFilterValues'
passing xmltype(t.c)
columns
fo  varchar2(250) path '.'
) x;

解决方案SQL #

-- 创建测试表并插入数据（若已存在可跳过）
create table t(c clob);

insert into t values('<?xml version="1.0" encoding="UTF-8" standalone="yes"?><dataView><fieldOptions><fieldId>1</fieldId></fieldOptions><fieldOptions><fieldId>2</fieldId><quickFilterValues>100</quickFilterValues></fieldOptions><fieldOptions><fieldId>3</fieldId><quickFilterValues>10</quickFilterValues><quickFilterValues>20</quickFilterValues><quickFilterValues>30</quickFilterValues></fieldOptions></dataView>');

-- 按fieldId分组统计quickFilterValues数量
select
    fieldId,
    count_qfv as count
from t,
xmltable(
    '/dataView/fieldOptions'
    passing xmltype(t.c)
    columns
        fieldId varchar2(10) path 'fieldId',
        count_qfv number path 'count(./quickFilterValues)'
);

说明 #

• 先通过xmltable解析每个fieldOptions节点，提取对应的fieldId。
• 使用XPath函数count(./quickFilterValues)直接统计当前fieldOptions节点下quickFilterValues的数量，节点不存在时自动返回0，无需额外处理空值。
• 该方式直接按fieldId维度输出统计结果，完全匹配报表需求。

内容的提问来源于stack exchange，提问作者Lucian Lazar