generate XML from oracle tables

Question

Need to create one generic stored procedure which will take table name as input parameter and create xml file with below format. Xml file name should be table name.

    <XML>
    <TABLENAME></TABLENAME>
    <RECORDS>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    <RECORD>
              <COLNAME>AAA</COLNAME>
              <COLNAME>AAA</COLNAME>
    <RECORD>
    </RECORDS>
    </XML> 


<TABLENAME>  = actual table name(case should be same as in database)
<COLNAME> = actual column names from table(case should be same as in database)
Keep other tags as it is. Repeat <RECORD> tag for each row retrieved.

Answer 1

Oracle has a built-in function to get the contents of a table as XML:

create table t42(id number, str varchar2(10));
insert into t42 values (1, 'AA');
insert into t42 values (2, 'BB');

select dbms_xmlgen.getxmltype('select * from t42')
from dual;

DBMS_XMLGEN.GETXMLTYPE('SELECT*FROMT42')
----------------------------------------
<ROWSET>
 <ROW>
  <ID>1</ID>
  <STR>AA</STR>
 </ROW>
 <ROW>
  <ID>2</ID>
  <STR>BB</STR>
 </ROW>
</ROWSET>

You can add your own tags around that; could be done as a query but since you want a stored procedure:

create or replace function table_to_xml(table_name in varchar2) return xmltype as
  xml xmltype;
begin
  select xmlelement("XML",
      xmlelement(evalname(table_name),
        dbms_xmlgen.getxmltype('select * from "' || table_name || '"')))
  into xml
  from dual;

  return xml;
end table_to_xml;
/

select table_to_xml('T42') from dual;

TABLE_TO_XML('T42')
----------------------------------------
<XML><T42><ROWSET>
  <ROW>
    <ID>1</ID>
    <STR>AA</STR>
  </ROW>
  <ROW>
    <ID>2</ID>
    <STR>BB</STR>
  </ROW>
</ROWSET>
</T42></XML>

So this has the structure you want (well, I think, but see below), but has ROWSET and ROW instead of RECORDS and RECORD. That might not matter, it depends whether you're developing the format for this interface still. If it does matter then you can apply a further step to rename those nodes, or - more helpfully - use the dbms_xmlgen procedures setrowsettag and setrowtag, which is simple in your procedure (and demonstrated below).

I'm assuming what you showed as <TABLENAME></TABLENAME> was a mistake, and you want the records within that tag. If not, and you really do want that for some reason, change the query in the function to:

  select xmlelement("XML",
      xmlconcat(xmlelement(evalname(table_name), null),
      dbms_xmlgen.getxmltype('select * from "' || table_name || '"')))
  into xml
  from dual;

You can then write that out to a file any way you normally would; if you're calling from SQL*Plus etc. you could select and spool, or if you don't want it returned at all you could add UTL_FILE directive to write the file from within the procedure, but that would have to be to a directory object on the DB server, which might not be convenient.

Mostly for my own benefit as I don't do a lot with XML:

create or replace procedure table_to_xml_file(table_name in varchar2) as
  ctx dbms_xmlgen.ctxhandle;
  clb clob;
  file utl_file.file_type;
  buffer varchar2(32767);
  position pls_integer := 1;
  chars pls_integer := 32767;
begin
  ctx := dbms_xmlgen.newcontext('select * from "' || table_name || '"');
  dbms_xmlgen.setrowsettag(ctx, 'RECORDS');
  dbms_xmlgen.setrowtag(ctx, 'RECORD');

  select xmlserialize(document
        xmlelement("XML",
          xmlelement(evalname(table_name),
            dbms_xmlgen.getxmltype(ctx)))
      indent size = 2)
  into clb
  from dual;

  dbms_xmlgen.closecontext(ctx);

  file := utl_file.fopen('<directory>', table_name || '.xml', 'w', 32767);
  while position < dbms_lob.getlength(clb) loop
    dbms_lob.read(clb, chars, position, buffer);
    utl_file.put(file, buffer);
    utl_file.fflush(file);
    position := position + chars;
  end loop;
  utl_file.fclose(file);
end table_to_xml_file;
/

When run with exec table_to_xml_file('T42'), this produces a file called T42.xml in the server directory pointed to by the <directory> directory object, which contains:

<XML>
  <T42>
    <RECORDS>
      <RECORD>
        <ID>1</ID>
        <STR>AA</STR>
      </RECORD>
      <RECORD>
        <ID>2</ID>
        <STR>BB</STR>
      </RECORD>
    </RECORDS>
  </T42>
</XML>

Incidentally, I've put double-quotes around the table name in the select inside the dbms_xmlgen.getxmltype call. That's to meet the 'case should be same as in database' requirement for the table name; it has to be passed to the procedure in the correct case or it will error. That's simpler than trying to correct the case within the procedure somehow, which would be awkward, or impossible if you had two tables with the same name apart from the case. The columns names will be in the correct case anyway.