Oracle APEX中查询XMLType数据并显示为文本，应使用什么元素？

需求：查询原始XML格式数据并以带缩进的结构化文本形式显示，当前使用动态内容元素时显示为无格式单行文本，尝试Textarea元素出现错误，需找到合适的显示元素。

使用的SQL查询语句：

select xmlroot(
    xmlelement("vozidlo",
        xmlattributes(
            a.vyrobne_cislo as "vin",
            a.datum_vyroby as "dat_vyroby"
        ),
        xmlforest(
            c.nazov as "vyrobca",
            a.nazov_modelu as "model",
            a.farba as "farba",
            rtrim(a.kod_motora) as "kod_motor",
            rtrim(a.kod_prevodovky) as "kod_prevodovka",
            d.popis as "typ_karoserie",
            xmlagg(
                xmlelement("zaznam",
                    xmlattributes (
                        g.datum as "datum"
                    ),
                    xmlforest(
                        g.akcia as "popis",
                        g.kilometre as "kilometre"
                    )
                ) order by g.kilometre
            ) as "zaznamy"
        )
    )
, version '1.0') result
from vozidlo a
join model b on a.typ_karoserie = b.typ_karoserie
    and a.nazov_modelu = b.nazov_modelu
    and a.id_vyrobcu = b.id_vyrobcu
join vyrobca c on b.id_vyrobcu = c.id_vyrobcu
join typ_karoserie d on d.id_typu = b.typ_karoserie
left join (
    select e.vyrobne_cislo, f.datum, f.akcia, f.kilometre
    from vozidlo e, table(e.zaznamy) f) g on g.vyrobne_cislo = a.vyrobne_cislo
where a.vyrobne_cislo = :P7_VIN_VOZIDLA
group by a.vyrobne_cislo, a.datum_vyroby, c.nazov, a.nazov_modelu, a.farba, a.kod_motora, a.kod_prevodovky, d.popis
fetch first row only;

解决步骤 #

1. 先让SQL返回格式化的XML（关键） #

当前查询返回的是未格式化的XMLType，需要用XMLSERIALIZE函数将其转换为带缩进的CLOB，修改后的SQL如下：

select XMLSERIALIZE(
    DOCUMENT xmlroot(
        xmlelement("vozidlo",
            xmlattributes(
                a.vyrobne_cislo as "vin",
                a.datum_vyroby as "dat_vyroby"
            ),
            xmlforest(
                c.nazov as "vyrobca",
                a.nazov_modelu as "model",
                a.farba as "farba",
                rtrim(a.kod_motora) as "kod_motor",
                rtrim(a.kod_prevodovky) as "kod_prevodovka",
                d.popis as "typ_karoserie",
                xmlagg(
                    xmlelement("zaznam",
                        xmlattributes (
                            g.datum as "datum"
                        ),
                        xmlforest(
                            g.akcia as "popis",
                            g.kilometre as "kilometre"
                        )
                    ) order by g.kilometre
                ) as "zaznamy"
            )
        )
    , version '1.0')
    AS CLOB INDENT SIZE = 2
) result
from vozidlo a
join model b on a.typ_karoserie = b.typ_karoserie
    and a.nazov_modelu = b.nazov_modelu
    and a.id_vyrobcu = b.id_vyrobcu
join vyrobca c on b.id_vyrobcu = c.id_vyrobcu
join typ_karoserie d on d.id_typu = b.typ_karoserie
left join (
    select e.vyrobne_cislo, f.datum, f.akcia, f.kilometre
    from vozidlo e, table(e.zaznamy) f) g on g.vyrobne_cislo = a.vyrobne_cislo
where a.vyrobne_cislo = :P7_VIN_VOZIDLA
group by a.vyrobne_cislo, a.datum_vyroby, c.nazov, a.nazov_modelu, a.farba, a.kod_motora, a.kod_prevodovky, d.popis
fetch first row only;

INDENT SIZE = 2指定缩进为2个空格，可根据需求调整。

2. 选择合适的APEX显示元素 #

选项一：只读文本区域（Read Only Text Area）

• 创建只读文本区域元素
• 源类型选择「SQL查询」，输入上述修改后的SQL
• 调整元素的高度（比如设置为20行），确保能完整显示XML内容
• 此元素支持多行显示，且能保留缩进格式，不会出现Textarea的类型匹配错误

选项二：代码编辑器（Code Editor）

• 若使用APEX 19.2及以上版本，推荐使用代码编辑器元素
• 设置「语言」为XML，自动提供语法高亮
• 源类型选择「SQL查询」，输入修改后的SQL
• 体验更专业，适合查看结构化XML内容

选项三：优化动态内容元素

• 若坚持使用动态内容，需在SQL中返回带<pre>标签的内容，让浏览器保留格式：

select '<pre>' || XMLSERIALIZE(
    DOCUMENT xmlroot(
        xmlelement("vozidlo",
            xmlattributes(
                a.vyrobne_cislo as "vin",
                a.datum_vyroby as "dat_vyroby"
            ),
            xmlforest(
                c.nazov as "vyrobca",
                a.nazov_modelu as "model",
                a.farba as "farba",
                rtrim(a.kod_motora) as "kod_motor",
                rtrim(a.kod_prevodovky) as "kod_prevodovka",
                d.popis as "typ_karoserie",
                xmlagg(
                    xmlelement("zaznam",
                        xmlattributes (
                            g.datum as "datum"
                        ),
                        xmlforest(
                            g.akcia as "popis",
                            g.kilometre as "kilometre"
                        )
                    ) order by g.kilometre
                ) as "zaznamy"
            )
        )
    , version '1.0')
    AS CLOB INDENT SIZE = 2
) || '</pre>' result
from vozidlo a
join model b on a.typ_karoserie = b.typ_karoserie
    and a.nazov_modelu = b.nazov_modelu
    and a.id_vyrobcu = b.id_vyrobcu
join vyrobca c on b.id_vyrobcu = c.id_vyrobcu
join typ_karoserie d on d.id_typu = b.typ_karoserie
left join (
    select e.vyrobne_cislo, f.datum, f.akcia, f.kilometre
    from vozidlo e, table(e.zaznamy) f) g on g.vyrobne_cislo = a.vyrobne_cislo
where a.vyrobne_cislo = :P7_VIN_VOZIDLA
group by a.vyrobne_cislo, a.datum_vyroby, c.nazov, a.nazov_modelu, a.farba, a.kod_motora, a.kod_prevodovky, d.popis
fetch first row only;

• 创建动态内容元素，源类型选「SQL查询」，输入上述SQL即可显示带缩进的XML

内容的提问来源于stack exchange，提问作者Filip Zaťko