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