CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle XML Tables
Version 11.1
 
General
Source {ORACLE_HOME}/rdbms/admin/dbmsxmlt.sql
Dependencies
source$ tab$ type$

XMLType

   

DBA

ALL

USER

dba_tables all_tables user_tables
dba_tab_cols all_tab_cols user_tab_cols
dba_xml_tables all_xml_tables user_xml_tables
 
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;

SELECT table_name, table_type
FROM user_all_tables;

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 a30

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

set linesize 121

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 depth all

desc uwclass$schema

SELECT table_name, table_type
FROM user_all_tables;

col column_name format a30

SELECT table_name, column_name, segment_name, cache
FROM user_lobs;
 
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 uwclass$schema PURGE;
 
Related Topics
DBMS_XMLGEN
XML Functions
Tables
XMLQuery
XMLTable
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [191 users online]    © 2010 psoug.org