Oracle 12.2是否有内置函数实现多格式XML转JSON？

Oracle 12.2提供了原生能力实现XML到JSON的转换，无需依赖外部工具或XSLT，核心是结合XML解析函数与JSON构造函数完成转换，以下是具体实现方案：

1. 核心函数说明 #

• XMLTYPE：将CLOB格式的XML数据转换为Oracle可解析的XML类型。
• XMLQUERY：提取XML中的单个节点值。
• XMLTABLE：将XML中的重复节点转换为关系型数据集，配合JSON_ARRAYAGG聚合为JSON数组。
• JSON_OBJECT：构造JSON对象；JSON_ARRAYAGG：将多行数据聚合为JSON数组。

2. 示例XML转换实现 #

针对你提供的示例XML，以下SQL可直接完成转换：

WITH sample_xml AS (
  SELECT XMLTYPE(
    '<elementA>
      <firstName>snoopy</firstName>
      <lastName>brown</lastName>
      <favoriteNumbers>
        <value>1</value>
        <value>2</value>
        <value>3</value>
      </favoriteNumbers>
    </elementA>'
  ) AS xml_doc FROM dual
)
SELECT JSON_OBJECT(
  'elementA' VALUE JSON_OBJECT(
    'firstName' VALUE XMLQUERY('/elementA/firstName/text()' PASSING xml_doc RETURNING CONTENT).getStringVal(),
    'lastName' VALUE XMLQUERY('/elementA/lastName/text()' PASSING xml_doc RETURNING CONTENT).getStringVal(),
    'favoriteNumbers' VALUE JSON_OBJECT(
      'value' VALUE (
        SELECT JSON_ARRAYAGG(TO_NUMBER(val))
        FROM XMLTABLE('/elementA/favoriteNumbers/value/text()' PASSING xml_doc COLUMNS val VARCHAR2(100) PATH '.')
      )
    )
  )
) AS json_result
FROM sample_xml;

执行后输出符合预期的JSON结果（修正了你期望结果中的笔误，实际对应XML的value数组为[1,2,3]）：

{
  "elementA": {
    "firstName": "snoopy",
    "lastName": "brown",
    "favoriteNumbers": {
      "value": [1,2,3]
    }
  }
}

3. 封装为存储过程，处理多格式XML并写入目标表 #

假设源表为xml_source（含id主键、xml_data CLOB字段），目标表为json_target（含id、json_data CLOB字段），以下存储过程可批量处理4种不同格式的XML：

CREATE OR REPLACE PROCEDURE xml_to_json_processor IS
  CURSOR xml_records IS
    SELECT id, xml_data FROM xml_source;
  v_xml_doc XMLTYPE;
  v_result_json CLOB;
BEGIN
  FOR rec IN xml_records LOOP
    -- 将CLOB转换为XMLTYPE，处理非法XML字符
    v_xml_doc := XMLTYPE(DBMS_XMLGEN.convert(rec.xml_data, DBMS_XMLGEN.ENTITY_ENCODE));
    
    -- 根据XML根节点判断格式，分支处理
    CASE XMLQUERY('local-name(/*)' PASSING v_xml_doc RETURNING CONTENT).getStringVal()
      WHEN 'elementA' THEN
        -- 示例格式转换逻辑
        SELECT JSON_OBJECT(
          'elementA' VALUE JSON_OBJECT(
            'firstName' VALUE COALESCE(XMLQUERY('/elementA/firstName/text()' PASSING v_xml_doc RETURNING CONTENT).getStringVal(), ''),
            'lastName' VALUE COALESCE(XMLQUERY('/elementA/lastName/text()' PASSING v_xml_doc RETURNING CONTENT).getStringVal(), ''),
            'favoriteNumbers' VALUE JSON_OBJECT(
              'value' VALUE (
                SELECT JSON_ARRAYAGG(TO_NUMBER(val))
                FROM XMLTABLE('/elementA/favoriteNumbers/value/text()' PASSING v_xml_doc COLUMNS val VARCHAR2(100) PATH '.')
              )
            )
          )
        ) INTO v_result_json FROM dual;
      
      WHEN 'elementB' THEN
        -- 第二种XML格式的转换逻辑，示例：
        SELECT JSON_OBJECT(
          'elementB' VALUE JSON_OBJECT(
            'fieldX' VALUE XMLQUERY('/elementB/fieldX/text()' PASSING v_xml_doc RETURNING CONTENT).getStringVal()
          )
        ) INTO v_result_json FROM dual;
      
      WHEN 'elementC' THEN
        -- 第三种XML格式转换逻辑
        NULL;
      
      WHEN 'elementD' THEN
        -- 第四种XML格式转换逻辑
        NULL;
      
      ELSE
        -- 未知格式，跳过并记录日志
        DBMS_OUTPUT.PUT_LINE('跳过未知格式XML，ID: ' || rec.id);
        CONTINUE;
    END CASE;
    
    -- 写入目标表
    INSERT INTO json_target(id, json_data) VALUES(rec.id, v_result_json);
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('处理ID: ' || rec.id || ' 失败，错误信息: ' || SQLERRM);
END;
/

4. 关键注意事项 #

• 重复节点处理：对于XML中的重复子节点（如多个value），必须使用JSON_ARRAYAGG聚合，否则仅会提取第一个节点的值。
• 空值处理：使用COALESCE函数将空节点转换为空字符串，避免JSON输出null值。
• XML合法性：若CLOB中的XML包含未转义的特殊字符，需先用DBMS_XMLGEN.convert进行转义，否则XMLTYPE转换会报错。
• 性能优化：批量处理大量数据时，建议使用BULK COLLECT替代游标循环，减少上下文切换开销。

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