blog.gralike.com

begin
 dbms_xmlschema.registerSchema
(schemaURL => 'http://www.gralike.com/marco/HistorieWW/WWBase.xsd'
,schemaDoc => xdbURIType('/public/HistorieWW/WWBase.xsd').getClob()
,LOCAL     => FALSE
,genTypes  => TRUE
,genBean   => FALSE
,genTables => TRUE
);
end;
/

TRUNCATE TABLE hgo010_detam;
DROP TABLE hgo010_detam
;
 
CREATE TABLE hgo011_detam
(hgo_id number(12))
;
 
 
/*
 
select level, parent_table_column
from user_nested_tables
connect by prior table_name=parent_table_name
start with parent_table_name='HGO011_DETAM'
 
 
     LEVEL PARENT_TABLE_COLUMN
---------- ----------------------------------------------------
         1 "XMLDATA"."R801FINR"
         2 R802GEVALSNR
         3 "WWGEVAL"."DERDEREC"
         3 "WWGEVAL"."BETOPDRA"
         3 "WWGEVAL"."BETOPDRB"
         3 "WWGEVAL"."BETOPDRC"
         3 "R850INGANGHISTORIE"."R851PERIODIEKEANG"
         3 "R850INGANGHISTORIE"."R852RECHTRLT"
         3 "R860INGANGHISTORIE"."R861DAGLOONPERIODEWW"
         3 "R860INGANGHISTORIE"."R862TWRCHPERIODE"
         3 "R860INGANGHISTORIE"."R863AOPERIODE"
         3 "R860INGANGHISTORIE"."R864BSCPERIODE"
         3 "R860INGANGHISTORIE"."R865GEZINSINKOMENS"
         3 "R870INGANGHISTORIE"."R871INHBRUPERIODE"
         3 "R870INGANGHISTORIE"."R872SANCTIEPERIODE"
         3 "R870INGANGHISTORIE"."R873ZFPERIODE"
         3 "R870INGANGHISTORIE"."R874LHFPERIODE"
         3 "R870INGANGHISTORIE"."R875VSVORSALDI"
         3 "R870INGANGHISTORIE"."R876VAKANTIEPERIODE"
         3 "R880GEVALSNR"."R881VERHAAL"
         4 R882REGEL
         3 R840INGANGHISTORIE
         4 R841BETALINGSRLT
         4 R842VERREKENINGSRLT
         4 R843VVOBOEKING
         4 R844INSPERIODE
 
26 rows selected.
 
*/
 
ALTER TABLE hgo011_detam
ADD 
(GEGEVENS xmltype)
  xmltype COLUMN GEGEVENS
  STORE AS OBJECT RELATIONAL
  XMLSCHEMA "http://www.uwv.nl/ww/historie/detam/WWBase.xsd" 
  ELEMENT "WW-HISTORIE"
  VARRAY   gegevens."XMLDATA"."R801FINR" 
  STORE AS TABLE R801FINR_TABLE
  (contraint R801FINR_PKEY 
   PRIMARY KEY (NESTED_TABLE_ID, ARRAY_INDEX))
;