Adding an attribute to all nodes matching an XPATH expression using Oracle XML DB

Question

I cannot come to a solution to this task: my goal is to pass a cursor to a PL/SQL procedure and get the results as an XMLType. The function dbms_xmlgen.getxmltype() makes this task straightforward

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

Now I want to add the cursor column data type as an attribute to each corresponding XML element.

<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>

This could be done using dynamic SQL, so I can write a PL/SQL function to get an associative array mapping each column to the corresponding data type.

Supposing I have both the aforementioned associativa array and the XMLType, how can I apply a set of transformations using a XPATH expression such as

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

Any other approach to get the job done will be fine

Answer 1

You could convert your metadata information to its own XML representation, and then have an XPath that finds the matching entry:

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 '.');

Each ROWSET (there's only one, of course) generates a new ROWSET element; then each ROW under that generates a new ROW element; then each mode under that generates a new node with the same name and value, but the name is also used to find the matching entry in the metadata and extract it's type attribute and use it as an attribute for this node instead.

A worked example:

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;
/

Then an block that generates a cursor - similar to your example but with two rows just to check that works - and then calls the function to get the modified 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>

You may want more data types defined in the CASE, of course.