Oracle XML Tables
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose  
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;
/

COMMIT;

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