Oracle XML Tables
Version 11.2.0.3

General Information
Create {ORACLE_HOME}/rdbms/admin/dbmsxmlt.sql
Data Dictionary Objects
ALL_TABLES DBA_XML_TABLES USER_TABLES
ALL_TAB_COLS SOURCE$ USER_TAB_COLS
ALL_XML_TABLES TAB$ USER_XML_TABLES
DBA_TABLES TYPE$ XMLTYPE
DBA_TAB_COLS    
 
Create
Simple XML Table CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX;
CREATE TABLE xml_lob_tab OF XMLTYPE;

-- not all tables are in user_tables
SELECT table_name
FROM user_tables;

-- it is better to look at user_all_tables
SELECT table_name, table_type
FROM user_all_tables;

set linesize 121
col column_name format a30

SELECT table_name, column_name, segment_name, cache
FROM user_lobs;

desc xml_lob_tab

SELECT table_name, storage_type
FROM user_xml_tables;

col data_type format a20

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'XML_LOB_TAB';

SELECT owner, typecode, attributes, methods, instantiable
FROM all_types
WHERE type_name = 'XMLTYPE';

SELECT text
FROM all_source
WHERE name = 'XMLTYPE'
ORDER BY line;
Simple XML Table CREATE TABLE <table_name> OF SYS.XMLTYPE
XMLSCHEMA "<.xsd_doc>"
ELEMENT "<element_name>" ID <integer>
TABLESPACE <tablespace_name>;
CREATE TABLE uwclass$schema OF SYS.XMLTYPE
XMLSCHEMA "http://xmlns.oracle.com/xdb/XDBSchema.xsd"
ELEMENT "schema" ID 81
TABLESPACE uwdata;

desc uwclass$schema

set describe depth all

desc uwclass$schema

SELECT table_name, table_type
FROM user_all_tables;

SELECT table_name, column_name, segment_name, cache
FROM user_lobs;
Binary XML Table with Partitioning on a Virtual Column CREATE TABLE orders OF XMLType
XMLTYPE STORE AS BINARY XML
VIRTUAL COLUMNS (SITE_ID AS (XMLCast(XMLQuery('/Order/@SiteId' PASSING OBJECT_VALUE RETURNING CONTENT) AS NUMBER)))
PARTITION BY RANGE (site_id) (
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION pm VALUES LESS THAN (MAXVALUE));

DECLARE
 x XMLTYPE;
BEGIN
  x := XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
         <Order orderId="1" orderRevision="1" orderTimeStamp="01-JAN-2012">
           <OrderHeader>
             <AlternateIds>
               <AlternateId altIdType="SiteId">12</AlternateId>
               <AlternateId altIdType="MerchantOrderNumber">Merch</AlternateId>
               <AlternateId altIdType="MarketplaceOrderNumber">Place</AlternateId>
               <AlternateId altIdType="CustomerReferenceId">Ref</AlternateId>
               <AlternateId altIdType="CartId">Cart</AlternateId>
               <AlternateId altIdType="SessionId">1</AlternateId>
             </AlternateIds>
           </OrderHeader>
         </Order>');
  INSERT INTO orders VALUES (x);

  x := XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
         <Order orderId="1" orderRevision="1" orderTimeStamp="01-JAN-2012">
           <OrderHeader>
             <AlternateIds>
               <AlternateId altIdType="SiteId">245</AlternateId>
               <AlternateId altIdType="MerchantOrderNumber">Merch</AlternateId>
               <AlternateId altIdType="MarketplaceOrderNumber">Place</AlternateId>
               <AlternateId altIdType="CustomerReferenceId">Ref</AlternateId>
               <AlternateId altIdType="CartId">Cart</AlternateId>
               <AlternateId altIdType="SessionId">2</AlternateId>
             </AlternateIds>
           </OrderHeader>
         </Order>');
  INSERT INTO orders VALUES (x);
END;
/

SELECT * FROM orders PARTITION(P1);

SYS_NC_ROWINFO$
----------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
  <Order orderId="1" orderRevision="1" orderTimeStamp="01-JAN-2012">
    <OrderHeader>
      <AlternateIds>
        <AlternateId altIdType="SiteOrderNumber">123</AlternateId>
        <AlternateId altIdType="MerchantOrderNumber">Merch</AlternateId>
        <AlternateId altIdType="MarketplaceOrderNumber">Place</AlternateId>
        <AlternateId altIdType="CustomerReferenceId">Ref</AlternateId>
        <AlternateId altIdType="CartId">Cart</AlternateId>
        <AlternateId altIdType="SessionId">123</AlternateId>
      </AlternateIds>
    </OrderHeader>
  </Order>
 
Alter
Change Storage ALTER TABLE <table_name>
MODIFY LOB (<lob_name>)
(STORAGE (<storage_parameter>;
SELECT table_name, column_name, segment_name, cache
FROM user_lobs;

ALTER TABLE xml_lob_tab
MODIFY LOB (XMLDATA)
(STORAGE (BUFFER_POOL DEFAULT) CACHE);

SELECT table_name, column_name, segment_name, cache
FROM user_lobs;
 
Drop
Drop XML Table DROP TABLE <table_name> [PURGE];
DROP TABLE xml_lob_tab;

SELECT object_name, original_name, type, can_undrop, base_object
FROM recyclebin;

DROP TABLE uwclass$schema PURGE;

PURGE RECYCLEBIN;

Related Topics
DBMS_XMLGEN
Flashback Drop
Heap Tables
Pseudocolumns
XML Functions
XMLQuery
XMLTable

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved