Loading XML documents into an Oracle database (2)

If only I could do the following…(but it seems that it isn’t supported yet)…

That is…based on the first “Loading XML documents into an Oracle database (1)” post…

…build an XMLTYPE VIEW with an instead trigger moving the XML document REFERENCE values in one go in the varchar2() REFERENCE column, while inserting the XML document content in the XMLType column XML_VALUE…

SQL> -- while using the following sampleData.ctl SQL Loader control file

SQL> host cat sampleData.ctl

load data
infile 'sampleData.dat'
append
into table XML_VIEW
xmltype(XMLDATA) (
 filename filler char(120),
 XMLDATA  lobfile(filename) terminated by eof)

SQL> create table RELATIONAL_TABLE (
  2  	REFERENCE  VARCHAR2(100),
  3  	XML_VALUE  XMLTYPE
  4  )
  5  /

Table created.

Elapsed: 00:00:01.69

SQL> create or replace view XML_VIEW of xmltype
  2  with object id (
  3    xmlcast(XMLQuery('$X/PurchaseOrder/Reference' passing OBJECT_VALUE as "X" returning content) as varchar2(100))
  4  )
  5  as
  6  select XML_VALUE
  7    from RELATIONAL_TABLE
  8  /

View created.

Elapsed: 00:00:00.17

SQL> create or replace trigger DATA_LOADER
  2  instead of INSERT on XML_VIEW
  3  begin
  4  	     insert into RELATIONAL_TABLE values (
  5  	       XMLCAST(
  6  		 XMLQUERY(
  7  		   '/PurchaseOrder/Reference'
  8  		   passing :NEW.OBJECT_VALUE
  9  		   returning CONTENT
 10  		 )
 11  		 as VARCHAR2(100)
 12  	       ),
 13  	       :NEW.OBJECT_VALUE
 14    );
 15  end;
 16  /

Trigger created.

Elapsed: 00:00:00.07

SQL> host sqlldr &USERNAME/&PASSWORD@&TNSALIAS control=sampleData.ctl

SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jul 18 16:55:21 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
SQL*Loader-485: table XML_VIEW may not be loaded using a subtype

SQL> get sampleData.log
  1  SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jul 18 16:55:21 2014
  2  Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
  3  Control File:   sampleData.ctl
  4  Data File:      sampleData.dat
  5    Bad File:     sampleData.bad
  6    Discard File:  none specified
  7   (Allow all discards)
  8  Number to load: ALL
  9  Number to skip: 0
 10  Errors allowed: 50
 11  Bind array:     64 rows, maximum of 256000 bytes
 12  Continuation:    none specified
 13  Path used:      Conventional
 14  Table XML_VIEW, loaded from every logical record.
 15  Insert option in effect for this table: APPEND
 16	Column Name		     Position	Len  Term Encl Datatype
 17  ------------------------------ ---------- ----- ---- ---- ---------------------
 18  FILENAME				 FIRST	 120	       CHARACTER
 19    (FILLER FIELD)
 20  XMLDATA			       DERIVED	   *  EOF      CHARACTER
 21	 Dynamic LOBFILE.  Filename in field FILENAME
 22* SQL*Loader-485: table XML_VIEW may not be loaded using a subtype
 23  .

SQL> select REFERENCE
  2    from RELATIONAL_TABLE
  3  /

no rows selected

Elapsed: 00:00:00.02

Alas…”SQL*Loader-485: table XML_VIEW may not be loaded using a subtype”