如何使用Oracle SQL或PL/SQL从分号分隔字符串生成指定格式的XML

你想要生成的目标XML格式是这样的：

<person>
    <item value="Emma"/>
    <item value="John"/>
</person>

数据源则是像 Emma;John 这类分号分隔的字符串对吧？结合社区里@p3consulting和@Charlieface的建议，这里有个经过验证的最终解决方案：

select
    XMLElement("person",
        XMLAgg(
            XMLElement("item",
                XMLAttributes(str as "value")
            )
        )
    ).getStringVal()
from
    (
    select regexp_substr('Emma;John', '[^;]+', 1, level) as str
    from dual
    connect by regexp_substr('Emma;John', '[^;]+', 1, level) is not null
    )

简单说下这个逻辑：

• 内层查询用 regexp_substr 搭配 connect by 把分号分隔的字符串拆成单独的行数据；
• 外层通过 XMLElement 分别创建<person>根节点和<item>子节点，并用XMLAttributes给<item>添加value属性；
• 最后用XMLAgg把所有<item>节点聚合到<person>节点下，再通过getStringVal()输出最终的XML字符串。

备注：内容来源于stack exchange，提问作者DZN