Returning Oracle SQL data as XML

Returning Oracle SQL data as XML

An earlier post illustrated how to extract information from XML inside PL/SQL. This post is an example of the reverse of this, in that it shows how to create an XML document from a SQL statement. These examples are based on creating an XML document providing details of the tables in the SCOTT schema but could easily be adapted to suit your needs.


This first example extract the list of tables names and uses a combination of the XMLRoot, XMLElement, XMLAgg, XMLAttributes and XMLForest functions to create the document :

SELECT XMLRoot(
         XMLElement("Tables",
           XMLAgg(XMLElement("Table",
             XMLAttributes(table_name),
               XMLForest(owner,
                         tablespace_name,
                         status,
                         num_rows)))
       ), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
  from all_tables
 where owner = 'SCOTT'

This example returns a document like so :

<?xml version="1.0" standalone="yes"?>
<Tables>
  <Table TABLE_NAME="DEPT">
    <OWNER>SCOTT</OWNER>
    <TABLESPACE_NAME>USERS</TABLESPACE_NAME>
    <STATUS>VALID</STATUS>
    <NUM_ROWS>4</NUM_ROWS>
  </Table>
</Tables>

etc etc.

To make this a little more useful (and complex!) we can add a nested query to return the column details in-line with the table information, like this :

SELECT XMLRoot(
         XMLElement("Tables",
          XMLAgg(XMLElement("Table",
             XMLAttributes(at.table_name),
               XMLForest(at.owner,
                         at.tablespace_name,
                         at.status,
                         at.num_rows,
                         (select XMLAgg(XMLElement("Column",
                                   XMLAttributes(atc.column_name),
                                     XMLForest(atc.data_type,
                                               atc.data_length,
                                               atc.data_scale,
                                               atc.data_precision,
                                               atc.nullable)))
                           from all_tab_columns atc
                          where atc.owner = at.owner
                            and atc.table_name = at.table_name) as columns)))
         ), VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
  from all_tables at
 where owner = 'SCOTT'

which returns an XML document like so :

<?xml version="1.0" standalone="yes"?>
<Tables>
  <Table TABLE_NAME="DEPT">
    <OWNER>SCOTT</OWNER>
    <TABLESPACE_NAME>USERS</TABLESPACE_NAME>
    <STATUS>VALID</STATUS>
    <NUM_ROWS>4</NUM_ROWS>
    <COLUMNS>
      <Column COLUMN_NAME="DEPTNO">
        <DATA_TYPE>NUMBER</DATA_TYPE>
        <DATA_LENGTH>22</DATA_LENGTH>
        <DATA_SCALE>0</DATA_SCALE>
        <DATA_PRECISION>2</DATA_PRECISION>
        <NULLABLE>N</NULLABLE>
      </Column>
      <Column COLUMN_NAME="DNAME">
        <DATA_TYPE>VARCHAR2</DATA_TYPE>
        <DATA_LENGTH>14</DATA_LENGTH>
        <NULLABLE>Y</NULLABLE>
      </Column>
      <Column COLUMN_NAME="LOC">
       <DATA_TYPE>VARCHAR2</DATA_TYPE>
       <DATA_LENGTH>13</DATA_LENGTH>
       <NULLABLE>Y</NULLABLE>
     </Column>
    </COLUMNS>
  </Table>
</Tables>

etc etc.
I hope this is helpful to someone.  Most of the documentation that supports this post can be found here.

 


Comments (0)


Add a Comment

Please login to comment.