Oracle使用XMLFOREST生成XML时CLOB列转义问题求助

问题描述 #

表test_xml包含CLOB类型列ADDITIONAL_HEADER_INFO（存储XML格式内容，例如<ID name='BSA_NUMBER'>123</ID>）和若干VARCHAR2(100)类型列（如SHIPMENT_NUMBER）。使用XMLFOREST生成XML时，CLOB列中的XML标签会被转义（如<变为<），无法作为原生XML节点嵌入最终输出。

原查询语句 #

SELECT (XMLELEMENT (
    NAME "File",
    XMLAGG (
        XMLELEMENT (
            NAME "Invoice",
            XMLFOREST (
                SHIPMENT_NUMBER AS "SHIPMENTNUMBER",
                ADDITIONAL_HEADER_INFO AS "Invoice1")))))
FROM test_xml
WHERE 1 = 1 AND CONTROL_ID = 18644;

当前错误输出（转义后的XML） #

<?xml version="1.0" encoding="UTF-8"?>
<File>
   <Invoice>
      <SHIPMENTNUMBER>456</SHIPMENTNUMBER>
      <Invoice1>&lt;ID name='BSA_NUMBER'&gt;123&lt;/ID&gt;</Invoice1>
   </Invoice>
</File>

期望输出（原生XML节点嵌入） #

<?xml version="1.0" encoding="UTF-8"?>
<File>
   <Invoice>
      <SHIPMENTNUMBER>456</SHIPMENTNUMBER>
      <Invoice1><ID name='BSA_NUMBER'>123</ID></Invoice1>
   </Invoice>
</File>

解决方法 #

核心思路是将CLOB列的内容转换为XMLType，让Oracle识别其为原生XML对象而非普通文本，从而避免转义处理。

方案一：拆分XMLFOREST与XMLELEMENT #

SELECT XMLELEMENT(
    NAME "File",
    XMLAGG(
        XMLELEMENT(
            NAME "Invoice",
            XMLFOREST(SHIPMENT_NUMBER AS "SHIPMENTNUMBER"),
            XMLELEMENT(NAME "Invoice1", XMLTYPE(ADDITIONAL_HEADER_INFO))
        )
    )
)
FROM test_xml
WHERE CONTROL_ID = 18644;

方案二：XMLFOREST内直接转换XMLType #

SELECT XMLELEMENT(
    NAME "File",
    XMLAGG(
        XMLELEMENT(
            NAME "Invoice",
            XMLFOREST(
                SHIPMENT_NUMBER AS "SHIPMENTNUMBER",
                XMLTYPE(ADDITIONAL_HEADER_INFO) AS "Invoice1"
            )
        )
    )
)
FROM test_xml
WHERE CONTROL_ID = 18644;

原理说明 #

XMLTYPE()函数会将CLOB中的XML字符串解析为原生XML对象，Oracle在生成最终XML时，会直接嵌入该对象的节点结构，而非将其当作普通文本进行转义处理。

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