Oracle XML Tables

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;