Oracle 19c含非法字符列名的XML转换与解析问题

直接通过自定义函数对齐Oracle的XML非法字符转义规则（将非法字符转为_xHHHH_格式的Unicode编码），实现双向映射：

• 列名转XML属性名（转义）：

CREATE OR REPLACE FUNCTION col_to_xml_attr(p_col_name VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  -- 覆盖常见XML非法字符，可根据实际列名扩展
  RETURN REPLACE(
           REPLACE(
             REPLACE(p_col_name, '#', '_x0023_'),
             '&', '_x0026_'),
             '<', '_x003C_'
           );
END;
/

• XML属性名转列名（反转义）：

CREATE OR REPLACE FUNCTION xml_attr_to_col(p_attr_name VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  -- 通用反转义逻辑，自动识别所有_xHHHH_格式的编码
  RETURN UTL_I18N.UNESCAPE_REFERENCE(REPLACE(p_attr_name, '_x', '&#x'));
END;
/

用DBMS_XMLGEN包自动处理列名转义，高效生成包含所有行的单份CLOB格式XML：

DECLARE
  v_xml_ctx DBMS_XMLGEN.ctxhandle;
  v_result_clob CLOB;
BEGIN
  -- 初始化上下文，指定查询语句
  v_xml_ctx := DBMS_XMLGEN.newcontext('SELECT * FROM t1');
  
  -- 设置XML根节点和行节点标签
  DBMS_XMLGEN.setrowsettag(v_xml_ctx, 'data_rows');
  DBMS_XMLGEN.setrowtag(v_xml_ctx, 'record');
  
  -- 生成XML，自动将非法列名转义为合法属性名
  v_result_clob := DBMS_XMLGEN.getxml(v_xml_ctx);
  
  -- 可选：将XML保存到临时表或直接使用
  INSERT INTO xml_temp_table (xml_content) VALUES (v_result_clob);
  
  DBMS_XMLGEN.closecontext(v_xml_ctx);
  COMMIT;
END;
/

如果需要自定义XML结构（比如指定属性顺序），可结合转义函数手动构造：

SELECT XMLELEMENT("data_rows",
         XMLAGG(
           XMLELEMENT("record",
             XMLATTRIBUTES(
               t1."##name" AS col_to_xml_attr('##name'),
               t1.id AS 'id',
               t1.value AS 'value'
             )
           ) ORDER BY t1.id
         )
       ).getclobval() AS xml_content
FROM t1;

利用动态SQL结合反转义函数，自动映射转义后的属性名到原列名，避免静态路径匹配失败：

DECLARE
  v_xml CLOB := (SELECT xml_content FROM xml_temp_table WHERE id = 1); -- 获取目标XML
  v_col_mappings VARCHAR2(4000);
  v_insert_sql VARCHAR2(4000);
BEGIN
  -- 提取XML属性名，生成反转义后的列名映射
  SELECT LISTAGG(
           q'[XMLQUERY('$r/@' || attr_name || ' returning content') AS "' || xml_attr_to_col(attr_name) || '"', ', '
         ) WITHIN GROUP (ORDER BY attr_name)
  INTO v_col_mappings
  FROM (
    SELECT DISTINCT XMLCAST(
                      XMLQUERY('$attr/local-name()' PASSING VALUE(attr) AS "attr" RETURNING CONTENT)
                      AS VARCHAR2(100)
                    ) AS attr_name
    FROM XMLTABLE('/data_rows/record/@*' PASSING XMLTYPE(v_xml)) attr
  );
  
  -- 构造动态INSERT语句
  v_insert_sql := 'INSERT INTO t2 (' ||
                  REPLACE(v_col_mappings, q'[XMLQUERY('$r/@' || attr_name || ' returning content') AS "', '') || ') ' ||
                  'SELECT ' || v_col_mappings || ' ' ||
                  'FROM XMLTABLE(''/data_rows/record'' PASSING XMLTYPE(:xml_data) ' ||
                  'COLUMNS ' || v_col_mappings || ')';
  
  -- 执行导入
  EXECUTE IMMEDIATE v_insert_sql USING v_xml;
  COMMIT;
END;
/

若XML结构固定，也可手动指定转义后的属性路径：

INSERT INTO t2 ("##name", id, value)
SELECT 
  XMLCAST(XMLQUERY('/data_rows/record/@_x0023__x0023_name' PASSING XMLTYPE(v_xml) RETURNING CONTENT) AS VARCHAR2(100)),
  XMLCAST(XMLQUERY('/data_rows/record/@id' PASSING XMLTYPE(v_xml) RETURNING CONTENT) AS NUMBER),
  XMLCAST(XMLQUERY('/data_rows/record/@value' PASSING XMLTYPE(v_xml) RETURNING CONTENT) AS VARCHAR2(200))
FROM XMLTABLE('/data_rows/record' PASSING XMLTYPE(v_xml));

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