XMLType Datatype In Oracle9i

 

Oracle9i has a dedicated XML datatype called XMLTYPE. It is made up of a CLOB to store the original XML data and a number of member functions to make the data available to SQL. In this article I'll present a simple example of it's use.

First we must create a table to store XML documents using the XMLTYPE datatype:

CREATE TABLE tab1 (
  col1  SYS.XMLTYPE
);

The table can be populated using XML from a CLOB, VARCHAR2 or an XMLTYPE generated from a query:

DECLARE
  v_xml   SYS.XMLTYPE;
  v_doc   CLOB;
BEGIN
  -- XMLTYPE created from a CLOB
  v_doc := '<?xml version="1.0"?>' || Chr(10) || ' <TABLE_NAME>MY_TABLE</TABLE_NAME>';
  v_xml := sys.xmltype.createXML(v_doc);

  INSERT INTO tab1 (col1) VALUES (v_xml);

  -- XMLTYPE created from a query
  SELECT SYS_XMLGen(table_name)
  INTO   v_xml
  FROM   user_tables
  WHERE  rownum = 1;

  INSERT INTO tab1 (col1) VALUES (v_xml);

  COMMIT;
END;
/

The data in the table can be viewed using the following query:

SET LONG 1000
SELECT a.col1.getStringVal()
FROM   tab1 a;

A.COL1.GETSTRINGVAL()
----------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
 <TABLE_NAME>MY_TABLE</TABLE_NAME>

<?xml version="1.0"?>
<TABLE_NAME>TAB1</TABLE_NAME>

2 rows selected.

SQL>

We can extract the value of specific tags using:

SELECT a.col1.extract('//TABLE_NAME/text()').getStringVal() AS "Table Name"
FROM   tab1 a
WHERE  a.col1.existsNode('/TABLE_NAME')  = 1;

Table Name
----------------------------------------------------------------------------------------------------
MY_TABLE
TAB1

2 rows selected.

SQL>

In the above example I was expecting a string, but NUMBERs and CLOBs can be returned using getNumVal() and getClobVal() respectively. Since the XMLTYPE datatype can contain any XML document it is sensible to limit the query to those rows which contain the relevant tags, hence the WHERE clause.

 

 

DECLARE
enq_msgid      RAW(16);
eopt           dbms_aq.enqueue_options_t;
mprop          dbms_aq.message_properties_t;
xd    sys.XMLType ;
str  varchar(32767);
BEGIN

    xd := sys.XMLType.createXML(
    '<ControlParam>' ||
    '    <IFID>EAIG0318X01001</IFID>' ||
    '    <TXID>20100714_104250_osb_0000000001</TXID>' ||
    '</ControlParam>'
    );
    
    dbms_aq.enqueue(
          queue_name => 'XXSB_BILL.XXSB_IN_QUEUE',
          enqueue_options => eopt,
          message_properties => mprop,
          payload => xd,
          msgid => enq_msgid);

commit;
END;