Oracle中如何无需XSLT将任意SQL游标结果集转为列名作为属性的XML

可以通过PL/SQL结合DBMS_SQL包实现需求，直接从游标元数据中获取列名，逐行拼接符合要求的XML结构，避免XSLT的性能瓶颈。

实现函数 #

以下是一个可复用的函数，接收SYS_REFCURSOR作为输入，返回目标格式的XML（CLOB类型，支持大数据量）：

CREATE OR REPLACE FUNCTION cursor_to_custom_xml(p_cursor IN SYS_REFCURSOR) RETURN CLOB IS
  l_cursor_id    INTEGER;
  l_col_count    INTEGER;
  l_col_names    DBMS_SQL.DESC_TAB;
  l_col_types    DBMS_SQL.DESC_TAB;
  l_value        VARCHAR2(4000);
  l_date_var     DATE;
  l_xml          CLOB;
  l_row_xml      VARCHAR2(32767);
BEGIN
  -- 初始化XML根节点
  l_xml := '<ROWSET>' || CHR(10);

  -- 将输入游标转换为DBMS_SQL可处理的游标ID
  l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor);

  -- 获取游标列的名称和类型信息
  DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_id, l_col_count, l_col_names, l_col_types);

  -- 动态定义列变量，适配不同数据类型
  FOR i IN 1..l_col_count LOOP
    CASE l_col_types(i).col_type
      WHEN 12 THEN -- 日期类型
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, i, l_date_var);
      ELSE -- 字符、数字等类型
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, i, l_value, 4000);
    END CASE;
  END LOOP;

  -- 循环读取游标每一行数据
  WHILE DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 LOOP
    l_row_xml := '  <ROW>' || CHR(10);

    -- 处理每一列，生成带name属性的COLUMN元素
    FOR i IN 1..l_col_count LOOP
      CASE l_col_types(i).col_type
        WHEN 12 THEN
          DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_date_var);
          l_value := TO_CHAR(l_date_var, 'YYYY-MM-DD HH24:MI:SS');
        ELSE
          DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_value);
      END CASE;

      -- 转义XML特殊字符，避免格式错误
      l_row_xml := l_row_xml || '    <COLUMN name="' || l_col_names(i).col_name || '">' ||
                   CASE WHEN l_value IS NOT NULL THEN DBMS_XMLGEN.CONVERT(l_value) ELSE '' END ||
                   '</COLUMN>' || CHR(10);
    END LOOP;

    l_row_xml := l_row_xml || '  </ROW>' || CHR(10);
    DBMS_LOB.APPEND(l_xml, l_row_xml);
  END LOOP;

  -- 闭合XML根节点
  l_xml := l_xml || '</ROWSET>';

  -- 关闭游标
  DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

  RETURN l_xml;
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(l_cursor_id) THEN
      DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
    END IF;
    RAISE;
END;
/

关键逻辑说明 #

• 游标元数据获取：通过DBMS_SQL.DESCRIBE_COLUMNS获取输入游标的列名和数据类型，支持任意结构的游标。
• 数据类型适配：单独处理日期类型，统一转换为指定格式的字符串；其他类型直接读取并转义特殊字符。
• 高效拼接：使用DBMS_LOB.APPEND拼接CLOB，避免VARCHAR2长度限制，同时保证大数据量下的性能。
• XML格式安全：用DBMS_XMLGEN.CONVERT处理特殊字符（如<、>、&），生成合法的XML。

使用示例 #

调用函数转换任意游标：

DECLARE
  l_cursor SYS_REFCURSOR;
  l_result CLOB;
BEGIN
  -- 替换为你的查询语句
  OPEN l_cursor FOR SELECT ID, NAME, AGE FROM your_table;
  
  l_result := cursor_to_custom_xml(l_cursor);
  
  -- 输出结果（若数据量过大，建议写入文件或表）
  DBMS_OUTPUT.PUT_LINE(l_result);
END;
/

注意事项 #

• 若游标包含长度超过4000的字符列，可将l_value改为CLOB类型，并调整DBMS_SQL.DEFINE_COLUMN的参数。
• 可根据需求修改日期格式、NULL值的处理逻辑（比如保留<COLUMN name="XXX"></COLUMN>还是省略）。

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