Export a table or a query result in Oracle as XML-file using XMLTYPE.

Today I would like to talk about how I generate dynamically a code to export a table or a query result as XML file using XMLTYPE.

In many situations I need to save a table or query result as XML-file in order to use the file as configuration for my application or to use it in other program (Java, C#, …). To achieve this I wrote the following PL/SQL Block:

declare
  -- table or view to export
  v_tbl_name varchar2(30) := 'MYTABLE';
  v_stmt varchar2(32767);
  --the table will be saved in this variable
  v_output_as_xmltype XMLTYPE;
  function fu_format(p_name varchar2, p_type varchar2) return varchar2 is
    ret varchar2(200);
  begin
    --on can add e.g formating for timestamp...
    ret := case when p_type = 'DATE' then 'to_char('||p_name||',''dd.mm.yyyy'')' else p_name end;  
    return lpad(' ',15) || ret;
  end fu_format;
begin
  for rec in (select column_name,data_type from user_tab_cols where table_name = v_tbl_name order by column_id) loop
    v_stmt := v_stmt || fu_format(rec.column_name,rec.data_type) || ' as ' || rec.column_name || ',' || chr(10);
  end loop;
  v_stmt := 'SELECT XMLELEMENT("'||v_tbl_name||'",' || chr(10) ||
           'XMLAGG( ' || chr(10) ||
           '  XMLELEMENT("row", ' || chr(10) ||
           '    XMLFOREST( ' || chr(10) ||
           rtrim(v_stmt,','||chr(10)) || chr(10) ||
           '    ) ' || chr(10) ||
           '  ) ' || chr(10) ||
           ') ' || chr(10) ||
           ') FROM ' || v_tbl_name 
         ;
  -- this is the statement
  dbms_output.put_line(v_stmt);
  execute immediate v_stmt into v_output_as_xmltype;
  --one can save the v_output_as_xmltype in a file
  -- or just insert it in a table
  -- ddl for the xml_output_table
  --create table xml_output_table(id number,content xmltype);
  INSERT INTO xml_output_table VALUES (1, v_output_as_xmltype);
  COMMIT;
end;

Let create a table to test this block:

create table mytable(
                     id number
                    ,col1 varchar2(50)
                    ,col2 varchar2(50)
                    ,col3 varchar2(50)
                    ,created_at date
                );
insert into mytable select   rownum
                          ,'col1_'||rownum
                          ,'col2_'||rownum
                          ,'col3_'||rownum
                          ,sysdate-rownum 
                     from dual connect by level < 4;
3 rows inserted.
commit;

One have only to set the table (or view) name in the block above to generate following code:

SELECT XMLELEMENT("MYTABLE1",
XMLAGG( 
  XMLELEMENT("row", 
    XMLFOREST( 
               ID as ID,
               COL1 as COL1,
               COL2 as COL2,
               COL3 as COL3,
               to_char(CREATED_AT,'dd.mm.yyyy') as CREATED_AT
    ) 
  ) 
) 
) FROM MYTABLE

The content of the XML file is:

<MYTABLE>
  <row>
    <ID>1</ID>
    <COL1>col1_1</COL1>
    <COL2>col2_1</COL2>
    <CREATED_AT>01.02.2019</CREATED_AT>
  </row>
  <row>
    <ID>2</ID>
    <COL1>col1_2</COL1>
    <COL2>col2_2</COL2>
    <CREATED_AT>31.01.2019</CREATED_AT>
  </row>
  <row>
    <ID>3</ID>
    <COL1>col1_3</COL1>
    <COL2>col2_3</COL2>
    <CREATED_AT>30.01.2019</CREATED_AT>
  </row>
</MYTABLE>

But what if you want to export a select statement with where clause instead of a table or a view? One possibility would be to create a view with the statement. An elegant way is to use the „dbms_sql“ package. With it you can get all necessary information about the statement (column name and type) to generate the SQL.

For example: select id,col1 from mytable where id < 5.

declare
  -- Statement 
  v_query varchar2(32767) := 'select id,col1 from MYTABLE where id < 5';
  v_stmt varchar2(32767);
  v_output_as_xmltype XMLTYPE;
  v_cursor_id integer;
  v_col_cnt integer;
  v_columns dbms_sql.desc_tab;
 
  function fu_format(p_name varchar2, p_type number) return varchar2 is
    ret varchar2(200);
  begin
    ret := case when p_type = dbms_types.TYPECODE_DATE  then 'to_char('||p_name||',''dd.mm.yyyy'')' else p_name end;  
    return lpad(' ',15) || ret;
  end fu_format;
begin
  v_cursor_id := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor_id, v_query, dbms_sql.native);
  dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);
  
  for i in 1 .. v_columns.count loop
    v_stmt := v_stmt || fu_format(v_columns(i).col_name,v_columns(i).col_type) || ' as ' || v_columns(i).col_name || ',' || chr(10);
  end loop;
    dbms_sql.close_cursor(v_cursor_id);
    v_stmt := 'SELECT XMLELEMENT("query",' || chr(10) ||
           'XMLAGG( ' || chr(10) ||
           '  XMLELEMENT("row", ' || chr(10) ||
           '    XMLFOREST( ' || chr(10) ||
           rtrim(v_stmt,','||chr(10)) || chr(10) ||
           '    ) ' || chr(10) ||
           '  ) ' || chr(10) ||
           ') ' || chr(10) ||
           ') text FROM ( ' || v_query || ' )' 
         ;
  dbms_output.put_line(v_stmt);
  --execute immediate v_stmt into v_output_as_xmltype;
  --INSERT INTO xml_tab VALUES (1, v_output_as_xmltype);
  --COMMIT;
  
end;

This will generate this statement:

SELECT XMLELEMENT("query",
XMLAGG( 
  XMLELEMENT("row", 
    XMLFOREST( 
               ID as ID,
               COL1 as COL1
    ) 
  ) 
) 
) text FROM ( select id,col1 from MYTABLE where id < 5 )

 You can easily write XMLTYPE object to a file.