Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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
sys.xmltype;
-- not all tables are in user_tables
SELECT table_name
FROM user_tables
ORDER BY 1;
-- 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
sys.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));
desc orders
set describe depth all
desc orders
set describe depth 1
desc orders
SELECT column_id, column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'ORDERS'
ORDER BY 1;
COLUMN_ID COLUMN_NAME VIR DATA_DEFAULT
---------- ------------------------------ --- --------------------------------------------
1 XMLDATA NO
1 SYS_NC_ROWINFO$ YES
SITE_ID YES CAST(SYS_XQ_UPKXML2SQL(
SYS_XQEXVAL(XMLQUERY('/Order/@SiteId'
PASSING BY VALUE SYS_MAKEXML( 0, "XMLDATA")
RETURNING CONTENT ), 0, 0, 16777216,
1073741824),50,1,2) AS NUMBER)
SYS_NC_OID$ NO SYS_OP_GUID()
SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'ORDERS';