====== XMLDB ======

===== Storage =====

==== Character object storage (schemaless) ====

Create Table:

	CREATE TABLE tableName
	(
	  ID NUMBER(19),
	  document XMLType
	)
	XMLTYPE COLUMN DOCUMENT STORE AS CLOB

==== Character object storage based on schema ====

Register Schema:

	declare
	xmlschema CLOB := '<?xml version="1.0" encoding="UTF-8"?>
	<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	...
	</xs:schema>';
	begin
	DBMS_XMLSCHEMA.registerSchema(
		SCHEMAURL  => 'SCHEMANAME',
		SCHEMADOC  => xmlschema,
		LOCAL      => TRUE,     -- local
		GENTYPES   => FALSE,     -- generate object types
		GENBEAN    => FALSE,     -- no java beans
		GENTABLES  => FALSE,     -- generate object tables
		FORCE      => FALSE
		);
	end;

Create Table:

	CREATE TABLE tableName
	(
	  ID NUMBER(19),
	  document XMLType
	)
	XMLTYPE COLUMN DOCUMENT STORE AS CLOB
	XMLSCHEMA SCHEMANAME ELEMENT "rootNodeName"

==== SQL Object storage based on schema ====

Register Schema:

	declare
	xmlschema CLOB := '<?xml version="1.0" encoding="UTF-8"?>
	<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	...
	</xs:schema>';
	begin
	DBMS_XMLSCHEMA.registerSchema(
		SCHEMAURL  => 'SCHEMANAME',
		SCHEMADOC  => xmlschema,
		LOCAL      => TRUE,     -- local
		GENTYPES   => TRUE,     -- generate object types
		GENBEAN    => FALSE,     -- no java beans
		GENTABLES  => TRUE,     -- generate object tables
		FORCE      => FALSE,
		OPTIONS    => DBMS_XMLSCHEMA.REGISTER_AUTO_OOL
		);
	end;

Create Table:
	CREATE TABLE tableName
	(
	  ID NUMBER(19),
	  document XMLType
	)
	XMLTYPE COLUMN DOCUMENT STORE AS OBJECT RELATIONAL
	XMLSCHEMA SCHEMANAME ELEMENT "rootNodeName"

==== Binary XML storage (schemaless) ====

Create Table:
	CREATE TABLE tableName
	(
	  ID NUMBER(19),
	  document XMLType
	)
	XMLTYPE COLUMN "DOCUMENT" STORE as BINARY XML

==== Binary XML storage (any schema) ====

Register Schema:

	declare
	xmlschema CLOB := '<?xml version="1.0" encoding="UTF-8"?>
	<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	...
	</xs:schema>';
	begin
	DBMS_XMLSCHEMA.registerSchema(
		SCHEMAURL  => 'SCHEMANAME',
		SCHEMADOC  => xmlschema,
		LOCAL      => TRUE,     -- local
		GENTYPES   => FALSE,     -- generate object types
		GENBEAN    => FALSE,     -- no java beans
		GENTABLES  => FALSE,     -- generate object tables
		FORCE      => FALSE,
		OPTIONS    => DBMS_XMLSCHEMA.REGISTER_BINARYXML
		);
	end;

Create Table:

	CREATE TABLE tableName
	(
	ID NUMBER(19),
	document XMLType
	)
	XMLTYPE COLUMN "DOCUMENT" STORE as BINARY XML
	ALLOW ANYSCHEMA

==== Binary XML storage based on schema ====

Register Schema:

	declare
	xmlschema CLOB := '<?xml version="1.0" encoding="UTF-8"?>
	<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
	...
	</xs:schema>';
	begin
	DBMS_XMLSCHEMA.registerSchema(
		SCHEMAURL  => 'SCHEMANAME',
		SCHEMADOC  => xmlschema,
		LOCAL      => TRUE,     -- local
		GENTYPES   => FALSE,     -- generate object types
		GENBEAN    => FALSE,     -- no java beans
		GENTABLES  => FALSE,     -- generate object tables
		FORCE      => FALSE,
		OPTIONS    => DBMS_XMLSCHEMA.REGISTER_BINARYXML
		);
	end;

Create Table:

	CREATE TABLE tableName
	(
	  ID NUMBER(19),
	  document XMLType
	)
	XMLTYPE COLUMN "DOCUMENT" STORE as BINARY XML
	XMLSCHEMA SCHEMANAME ELEMENT "rootNodeName"

===== Querying =====

==== XPath ====

  SELECT 
    extractValue(
        DOCUMENT
      , 
        './doc/ROOT/var[@name="PostalCode"]/subnode/value/text()'
    )
  FROM
    tableName
  WHERE
    rownum < 1000


==== XMLQuery ====

  SELECT XMLQuery(
    'count(.//*)' 
    PASSING BY VALUE p.DOCUMENT 
    RETURNING CONTENT
  ) 
  FROM TABLE_NAME p 

==== XMLTABLE ====

  SELECT
    tableName.ID
  ,
    o.varname
  ,
    p.pvalue
  FROM 
    tableName
  ,
    XMLTable(
      './doc/ROOT/var'
      PASSING DOCUMENT
      COLUMNS
        varname varchar2(20) PATH '@name', 
        var XMLType PATH './subnode' 
    ) as o
  ,
    XMLTable('./subnode/value'
      PASSING o.var
      COLUMNS
        pvalue varchar2(4) PATH './text()'
    ) as p
  WHERE 
    o.varname = 'PostalCode'
  and
    p.pvalue < 1160

====== Indexing ======

===== Function based indexing =====

  CREATE INDEX 
    indexName
  ON 
    tableName 
  extractValue(
    DOCUMENT
  , 
    './doc/ROOT/var[@name="PostalCode"]/subnode/value/text()'
  );

===== XMLIndex =====

  CREATE INDEX 
    po_xmlindex_ix
  ON
    po_clob (OBJECT_VALUE) 
  INDEXTYPE IS XDB.XMLINDEX
  PARAMETERS (
    'PATHS (INCLUDE (
      /PurchaseOrder/LineItems//*
      /PurchaseOrder/Reference
    ))'
  );