Generate code to export XML

Peter Goldthorp June 2002

xmlgen uses native dynamic pl/sql to generate an xml file for a given table and where clause. It builds a cursor to select the columns in a table and uses the output of this to build a second cursor which generates the xml. xml_export is a wrapper that calls xmlgen for a given folder. Performance will be better if regular sql is used instead of dynamic calls. proc_template is provided for this.

proc_template takes a base table e.g. CI_TABLE_DEFINITIONS as an input parameter and generates a pl/sql procedure for inclusion in the casexml utility. It is called from a browser and generates a .sql file for download.

create or replace package xmlutil as

procedure proc_template(base_table in varchar2);
procedure xmlgen(c_base_table in varchar2
                  , c_where in varchar2 := 'where 1=1');

  procedure xml_export(n_folder_irid in sdd_folders.irid%type);


end xmlutil;
/

create or replace package body xmlutil as

procedure proc_template(base_table in varchar2)
is

  type cur_type is ref cursor;
  tab_cursor      cur_type;
  col_cursor      cur_type;
  htp_text        varchar2(32000) := '';
  cur_text        varchar2(32000) := 'select ';
  select_column   varchar2(256) := '';
  tab_text        varchar2(32000);

  v_first         boolean := TRUE;

begin

  tab_text := 'select   column_name
  from all_tab_columns
  where table_name = :b_table
  order by column_id';
  open tab_cursor for tab_text using base_table;
  loop
    fetch tab_cursor into select_column;
    exit when tab_cursor%notfound;
    if v_first
      then v_first := FALSE;
    else
      cur_text := cur_text || chr(10)||'    ,      ';
    end if;
    
    cur_text:= cur_text || select_column;
    htp_text:= htp_text || '     htp.p(''<'||select_column||'>'''||'||c_rec.'
                        || select_column||'||'|| '''</'||select_column||'>'');'||chr(10);
  end loop;

  cur_text := cur_text || '
     from '|| base_table ||'
     where irid = n_irid;';

  htp.p('Content-type: application/octet-stream'); 
  htp.p('Content-Disposition: attachment; filename='||base_table||'.sql'); 
  htp.p('Content-Transfer-Encoding: base64');	
  htp.p('');

htp.p(' procedure get_'||base_table||'(n_irid in sdd_object_versions.irid%type)
  is
    cursor cur_element(n_irid in sdd_object_versions.irid%type)
    is');
  htp.p('    '||cur_text);
htp.p(
'begin
   for c_rec in cur_element(n_irid) loop
');
  htp.p(htp_text);
htp.p(
'  end loop;
end get_'||base_table||';');
  close tab_cursor;
end proc_template;



procedure xmlgen(c_base_table in varchar2
                  , c_where in varchar2 := 'where 1=1')
is



  type cur_type is ref cursor;
  tab_cursor      cur_type;
  col_cursor      cur_type;
  tab_text        varchar2(32000) := '';
  col_text        varchar2(32000) := 'select ';

  select_column   varchar2(256) := '';
 
  v_buffer        varchar2(32000);


  v_first         boolean := TRUE;
  v_amount        NUMBER;
  v_posn          NUMBER := 1;




begin

  htp.p('<'||c_base_table||'>'||chr(10));
  tab_text := 'select column_name
               from   all_tab_columns
               where table_name = :b_table
               order by column_id';

  open tab_cursor for tab_text using c_base_table;
  loop
    fetch tab_cursor into select_column;
    exit when tab_cursor%notfound;
    if v_first
      then v_first := FALSE;
    else
      col_text := col_text || '|| chr(10) ||';
    end if;
    
    col_text:= col_text || '''<'||select_column||'>'''||'||'|| select_column||'||'
                        || '''</'||select_column||'>''';

  end loop;


  col_text := col_text || ' from '|| c_base_table || ' ' ||c_where;




  close tab_cursor;
--  htp.p('&ltQUERY>'||col_text||'</QUERY>');

  open col_cursor for col_text;
  loop
    fetch col_cursor into v_buffer;
    exit when col_cursor%notfound;
    htp.p('&ltROW>' || v_buffer || '</ROW>');
  end loop;  
  close col_cursor;

  htp.p(chr(10)||'</'||c_base_table||'>');
  
end xmlgen;

procedure xml_export(n_folder_irid in sdd_folders.irid%type)
is

  cursor cur_folder(n_folder_irid in sdd_folders.irid%type)
  is
  select name
  from sdd_folders
  where irid = n_folder_irid;

  cursor cur_folder_members(n_folder_irid in sdd_folders.irid%type)
  is
  select o.irid
  ,      rm.short_name  el_type
  from sdd_object_versions  o
  ,    rm_element_types     rm
  ,    sdd_folder_members   fm
  where fm.folder_reference = n_folder_irid
  and fm.member_object = o.irid
  and o.logical_type_id = rm.irid
  order by o.name;

  cursor cur_columns(n_irid in ci_table_definitions.irid%type)
  is
  select irid
  from ci_columns
  where table_reference = n_irid
  order by order_sequence;
  

  v_fname         sdd_folders.name%type;

begin
  if etrmnav.workarea_context_set
    then null;
  elsif
     etrmnav.workarea_context_set('GLOBAL SHARED WORKAREA')
       then null;
  end if;

  for f_rec in cur_folder(n_folder_irid) loop
    v_fname := f_rec.name;
  end loop;

  htp.p('Content-type: application/octet-stream'); 
  htp.p('Content-Disposition: attachment; filename='||v_fname||'.xml'); 
  htp.p('Content-Transfer-Encoding: base64');	
  htp.p('');

  htp.p('<?xml version="1.0" ?>');
  htp.p('&ltROWSET>');

  for m_rec in cur_folder_members(n_folder_irid) loop
  if m_rec.el_type = 'TBL' then
    xmlgen(c_base_table => 'CI_TABLE_DEFINITIONS'
         , c_where => 'where irid = '||m_rec.irid);
--    for c_rec in cur_columns(m_rec.irid) loop
--      xmlgen(c_base_table => 'CI_COLUMNS'
--           , c_where => 'where irid = '||c_rec.irid);
--  end loop;
  end if;
    
  end loop;
  htp.p('</ROWSET>');
end xml_export;
end xmlutil;
/
show errors package xmlutil
show errors package body xmlutil