DBMS_XMLGEN and XSLT Example

Generates XML from a SQL query that includes a bind variable. Uses an XSL transformation to remove /ROWSET/ROW and replace with /BUGFIX/DEFINITION. g_component.show_xml is an internal procedure that prints an xml doc using dbms_output.

declare
  v_xml        xmltype;
  v_sql        varchar2(250) := 'select * from g_bugfixes where bugfix_id=:bugfix';
  v_bugfix_id  number  := 1488743;
  v_ctx        dbms_xmlgen.ctxHandle;
  v_xslt       xmltype;
  v_type       varchar2(32) := 'BUGFIX';
begin
  v_xslt := xmltype('
               
               
                 <'||v_type||' VERSION="1.0">
                 
                  
                      
                        
                      
                 
                 
                 
               
               ');
  v_ctx := dbms_xmlgen.newContext(v_sql);
  dbms_xmlgen.setbindvalue(v_ctx, 'bugfix', v_bugfix_id);
  v_xml := dbms_xmlgen.getxmltype(v_ctx);
  g_component.show_xml(v_xml);
  v_xml := v_xml.transform(v_xslt);
  g_component.show_xml(v_xml);
end;
/