向使用 Oracle XML DB 与 XPATH 表达式匹配的所有节点添加属性

Question

我无法找到此任务的解决方案：我的目标是将游标传递给PL/SQL过程并以XMLType的形式获取结果。dbms_xmlgen.getxmltype()函数使此任务变得简单明了

<ROWSET>
 <ROW>
  <FIRST_NAME>John</FIRST_NAME>
  <LAST_NAME>Goodman</LAST_NAME>
  <HIRE_DATE>22-JUN-2011</HIRE_DATE>
 </ROW>
</ROWSET>

现在，我想将游标列数据类型作为属性添加到每个相应的 XML 元素中。

<ROWSET>
 <ROW>
  <FIRST_NAME type="VARCHAR2">John</FIRST_NAME>
  <LAST_NAME type="VARCHAR2">Goodman</LAST_NAME>
  <HIRE_DATE type="DATE">22-JUN-2011</HIRE_DATE>
 </ROW>
</ROWSET>

这可以使用动态SQL来完成，因此我可以编写一个PL/SQL函数来获取将每列映射到相应数据类型的关联数组。

假设我同时拥有上述关联数组和XMLType，如何使用XPATH表达式应用一组转换，例如

-- pseudocode ;)
func(myXMLType, '//FIRST_NAME', ?add attribute to the matching node?)

任何其他完成工作的方法都可以

Answer 1

您可以将元数据信息转换为其自己的 XML 表示形式，然后有一个 XPath 来查找匹配的条目：

select *
from xmltable('for $i in $x/ROWSET return (element {"ROWSET"} {
  for $j in $i/ROW
    return (element {"ROW"} {
      for $k in $j/*
        return (element {$k/name()} {
          attribute type { $m/metadata/column[@name=$k/name()]/@type },
          $k/text()
      } )
    } )
  } )'
  passing generated_xml as "x", metadata_xml as "m"
  columns result xmltype path '.');

每个 ROWSET(当然只有一个(都会生成一个新的 ROWSET 元素;然后，该元素下的每个 ROW 都会生成一个新的 ROW 元素;然后，该下的每个模式都会生成一个具有相同名称和值的新节点，但该名称也用于在元数据中查找匹配的条目并提取其 type 属性并将其用作此节点的属性。

一个工作的例子：

create or replace function cursor_to_xml(p_cursor sys_refcursor) return xmltype is
  l_cursor sys_refcursor;
  l_ctx dbms_xmlgen.ctxhandle;
  l_xmltype xmltype;
  l_cursor_num pls_integer;
  l_col_cnt pls_integer;
  l_desc_tab dbms_sql.desc_tab2;
  l_metadata varchar2(32767);
  l_result xmltype;
begin
  -- get generated XMl as shown in the question
  l_cursor := p_cursor;
  l_ctx := dbms_xmlgen.newcontext(querystring => l_cursor);
  l_xmltype := dbms_xmlgen.getxmltype(ctx => l_ctx);
  dbms_xmlgen.closecontext(ctx => l_ctx);
  -- use DBMS_SQL to get the data types
  l_cursor_num := dbms_sql.to_cursor_number(rc => l_cursor);
  dbms_sql.describe_columns2(c => l_cursor_num, col_cnt => l_col_cnt,
    desc_t => l_desc_tab);
  dbms_sql.close_cursor(l_cursor_num);
  -- manually create an XML version of the column name/data type mappings
  -- which could be extended easily to include length/scale/precision/etc.
  l_metadata := '<metadata>';
  for i in 1..l_desc_tab.count loop
      l_metadata := l_metadata || '<column name="' || l_desc_tab(i).col_name ||
        '" type="' || case l_desc_tab(i).col_type
          when 1 then 'VARCHAR2'
          when 2 then 'NUMBER'
          when 12 then 'DATE'
          -- ...
          end
        || '"/>';
  end loop;
  l_metadata := l_metadata || '</metadata>';
  -- use XMLTable with an XPath that deconstructs and reconstructs the
  -- generated XML to add an attribute with the type; this is passed two
  -- XML objects, referred to internally as $x and $m
  -- xmlserialize() formats the result with indentation; xmltype then just
  -- gets it back to that type - you may not need either really
  select xmltype(xmlserialize(document result as varchar2(4000) indent))
  into l_result
  from xmltable('for $i in $x/ROWSET return (element {"ROWSET"} {
    for $j in $i/ROW
      return (element {"ROW"} {
        for $k in $j/*
          return (element {$k/name()} {
            attribute type { $m/metadata/column[@name=$k/name()]/@type },
            $k/text()
        } )
      } )
    } )'
    passing l_xmltype as "x", xmltype(l_metadata) as "m"
    columns result xmltype path '.');
  return l_result;
end cursor_to_xml;
/

然后是一个生成游标的块 - 类似于您的示例，但有两行只是为了检查是否有效 - 然后调用该函数以获取修改后的 XML：

set serveroutput on;
declare
  l_cursor sys_refcursor;
begin
  open l_cursor for
    select cast('John' as varchar2(10)) as first_name,
      cast('Goodman' as varchar2(10)) as last_name,
      date '2011-06-22' as hire_date
    from dual
    union all
    select cast('Rhea' as varchar2(10)) as first_name,
      cast('Perlman' as varchar2(10)) as last_name,
      date '2012-07-23' as hire_date
    from dual;
  dbms_output.put_line(cursor_to_xml(l_cursor).getstringval);
end;
/
PL/SQL procedure successfully completed.
<ROWSET>
  <ROW>
    <FIRST_NAME type="VARCHAR2">John</FIRST_NAME>
    <LAST_NAME type="VARCHAR2">Goodman</LAST_NAME>
    <HIRE_DATE type="DATE">22-JUN-11</HIRE_DATE>
  </ROW>
  <ROW>
    <FIRST_NAME type="VARCHAR2">Rhea</FIRST_NAME>
    <LAST_NAME type="VARCHAR2">Perlman</LAST_NAME>
    <HIRE_DATE type="DATE">23-JUL-12</HIRE_DATE>
  </ROW>
</ROWSET>

当然，您可能希望在 CASE 中定义更多数据类型。