Oracle | XML Tables

Oracle XML Tables Version 18c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose XML tables are tables optimized for persistent retention of XML.

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-2015"> <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-2015"> <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-2015"> <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;