variable vxml clob;

declare
  context dbms_xmlgen.ctxtype;
begin
  context := dbms_xmlgen.newcontext('&query');
  dbms_xmlgen.setnullhandling(context, dbms_xmlgen.empty_tag);
  :vxml := dbms_xmlgen.getxml(context);
  dbms_xmlgen.closecontext(context);
end;
/
select *
from
   xmltable( '(#ora:view_on_null empty #) {
              for $a at $i in /ROWSET/ROW
                 ,$r in $a/*
                   return element ROW{
                                     element ROW_NUM{$i}
                                    ,element COL_NAME{$r/name()}
                                    ,element COL_VALUE{$r/text()}
                                    }
              }'
             passing xmltype(:vxml)
             columns
                row_num   int
               ,col_name  varchar2(30)
               ,col_value varchar2(100)
      );
