Posts Tagged ‘xml’

Returning Oracle SQL data as XML

Thursday, September 24th, 2009

Hi

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>


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>


etc etc.


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

Tags: , , , , , , ,
Posted in Oracle | No Comments »

Extracting XML information using PL/SQL

Monday, June 2nd, 2008

Since version 9, Oracle has had support for XML data built into the heart of the database.  Sometimes however, it is not immediately obvious how to get data from an XML string, particularly if, like me, it’s not something you do that often!

Here is a small code fragment which, given an XML ’string’ (xml_char) extracts either some attribute values or node text :

DECLARE
  xml_char VARCHAR2(1024);
  xml      xmltype;
  v_type   VARCHAR2(32);
  v_server VARCHAR2(32);
  v_port   VARCHAR2(32);
BEGIN
  -- create dummy XML document
  xml_char := '<?xml version="1.0"?>' ||
              '<CONNECTION>' ||
              '   <MAPSERVER type="Arcims">' ||
              '      <HOST>uranus</HOST>' ||
              '      <PORT>5300</PORT>' ||
              '   </MAPSERVER>' ||
              '</CONNECTION>';
  -- convert to XML from char type
  xml := xmltype.createxml(xml_char);
  -- extract some elements
  v_type   := xml.EXTRACT('/CONNECTION/MAPSERVER/@type').getstringval();
  v_server := xml.EXTRACT('/CONNECTION/MAPSERVER/HOST/text()').getstringval();
  v_port   := xml.EXTRACT('/CONNECTION/MAPSERVER/PORT/text()').getstringval();
  -- display them
  DBMS_OUTPUT.PUT_LINE('Type   = ' || v_type);
  DBMS_OUTPUT.PUT_LINE('Server = ' || v_server);
  DBMS_OUTPUT.PUT_LINE('Port   = ' || v_port);
END;

Firstly an XML ‘object’ is created from the xml_char XML string, we can then call the ‘EXTRACT’ method to get the relevant attribute or node value.

Tags: , , ,
Posted in Oracle | No Comments »