create user test identified by test;
 
grant xdbadmin, dba to test;
 
connect test/test
 
set autotace off
set long 100000000
set pages 5000
set lines 200
set trimspool on
col segment_name for a30

var schemaPath varchar2(256)
var schemaURL  varchar2(256)
 
begin
  :schemaURL := 'http://www.myserver.com/public/root.xsd';
  :schemaPath := '/public/root.xsd';
end;
/
 

-- XSD Schema
 
declare
  res boolean;
  xmlSchema xmlType := xmlType(
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           xmlns="http://www.myserver.com/public/root.xsd"
           targetNamespace="http://www.myserver.com/public/root.xsd"
           elementFormDefault="qualified" attributeFormDefault="unqualified"
           xdb:storeVarrayAsTable="true">
  <xs:element name="ROOT" xdb:defaultTable="ROOT_TABLE" xdb:maintainDOM="false">
    <xs:complexType xdb:maintainDOM="false">
      <xs:sequence>
        <xs:element name="ROOT_ID" type="xs:integer" xdb:SQLName="ID"/>
        <xs:element name="INFO" xdb:SQLName="INFO" xdb:SQLInline="false"
                    xdb:defaultTable="INFO_TABLE" xdb:maintainDOM="false"
                    maxOccurs="unbounded">
          <xs:complexType xdb:maintainDOM="false">
            <xs:sequence>
              <xs:element name="INFO_ID" type="xs:integer" xdb:SQLName="INFO_ID"
                          xdb:SQLType="INTEGER"/>
              <xs:element name="INFO_CONTENT" xdb:SQLName="INFO_CONTENT"
                          type="xs:string" xdb:maintainDOM="false"
                          xdb:SQLType="VARCHAR2"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
');
begin
if (dbms_xdb.existsResource(:schemaPath)) then
    dbms_xdb.deleteResource(:schemaPath);
end if;
 res := dbms_xdb.createResource(:schemaPath,xmlSchema);
end;
/
 
begin
  :schemaURL := 'http://www.myserver.com/public/root.xml';
  :schemaPath := '/public/root.xml';
end;
/
 
-- XML Instance
 

declare
  res boolean;
  xmlSchema xmlType := xmlType(
'<?xml version="1.0" encoding="UTF-8"?>
<ROOT xmlns="http://www.myserver.com/public/root.xsd">
  <ROOT_ID>0</ROOT_ID>
  <INFO>
    <INFO_ID>0</INFO_ID>
    <INFO_CONTENT>Text</INFO_CONTENT>
  </INFO>
</ROOT>');
begin
if (dbms_xdb.existsResource(:schemaPath)) then
    dbms_xdb.deleteResource(:schemaPath);
end if;
 res := dbms_xdb.createResource(:schemaPath,xmlSchema);
end;
/
 

begin
  DBMS_XMLSCHEMA.deleteSchema('http://www.myserver.com/public/root.xsd', 4);
end;
/
 

purge recyclebin;

alter session set events='31098 trace name context forever';
 
begin
 DBMS_XMLSCHEMA.registerURI(
   schemaURL    => 'http://www.myserver.com/public/root.xsd',
   schemaDocURI => '/public/root.xsd',
   local        => FALSE,  -- local
   genTypes     => TRUE,   -- generate object types
   genBean      => FALSE,  -- no java beans
   genTables    => TRUE    -- generate object tables
 );
end; 
/
 
select object_name, object_type  from user_objects order by 2;

DECLARE
  gen_name VARCHAR2 (4000);
BEGIN
  SELECT TABLE_NAME INTO gen_name FROM USER_NESTED_TABLES
    WHERE PARENT_TABLE_NAME = 'ROOT_TABLE';
  EXECUTE IMMEDIATE 'RENAME "' || gen_name || '"TO INFO_TABLE_REFLIST';
END;
/

SELECT TABLE_NAME FROM USER_NESTED_TABLES;

desc INFO_TABLE_REFLIST

select object_name, object_type  from user_objects order by 2;

-- Create scoped REF (pointers to the table)

ALTER TABLE INFO_TABLE_REFLIST ADD SCOPE FOR (COLUMN_VALUE) IS INFO_TABLE;

-- Create an index on the REF

CREATE INDEX reflist_idx ON INFO_TABLE_REFLIST (COLUMN_VALUE);

select * from tab;
 
-- insert some data
 
DECLARE
   XMLData xmlType := xmlType(xdbURIType ('/public/root.xml').getClob());
BEGIN
  for i in 1..1000
  loop
      insert into ROOT_TABLE
      VALUES
      (XMLData);
  end loop;
END;
/


-- randomize the data a little
 
update ROOT_TABLE
set object_value = updateXML(object_value,
                            '/ROOT/ID/text()',
                            substr(round(dbms_random.value*100),0,2)
                            );
 
 
 
update ROOT_TABLE
set object_value = updateXML(object_value,
                            '/ROOT/INFO/INFO_ID/text()',
                            substr(round(dbms_random.value*100),0,2)
                            );
 
commit;


select segment_name
,      segment_type
,      (bytes/(1024*1024)) "BYTES(MB)"
from user_segments
order by segment_type;
 
select dbms_metadata.get_ddl('TABLE','ROOT_TABLE') from dual;

select dbms_metadata.get_ddl('TABLE','INFO_TABLE') from dual;


select count(*) from root_table; 

select count(*) from INFO_TABLE;

 
set autotrace on

create index root_table_ix on root_table
(extractvalue(object_value,'/ROOT/INFO/INFO_ID'));

exec dbms_stats.gather_schema_stats('TEST');

select value(rt)
from root_table rt
where extractvalue(object_value,'/ROOT/INFO/INFO_ID')='99';