Create XML View

10:31:29 SQL> @scripts.sql
10:31:37 SQL> set echo on
10:31:37 SQL> set time on
10:31:37 SQL> alter session enable parallel query;


Session altered.


10:31:37 SQL> alter session enable parallel ddl;


Session altered.


10:31:37 SQL> alter session enable parallel dml;


Session altered.


10:31:37 SQL> DROP INDEX invoice_ix;
DROP INDEX invoice_ix
           *
ERROR at line 1:
ORA-01418: specified index does not exist 




10:31:37 SQL> CREATE INDEX invoice_ix
10:31:37   2  ON einvoice(object_value)
10:31:37   3  INDEXTYPE IS XDB.XMLINDEX PARALLEL
10:31:37   4  PARAMETERS ('
10:31:37   5  GROUP einvoiceparam XMLTable einvoice_idx_tab
10:31:37   6  ''/A0401/Invoice/Main''
10:31:37   7  COLUMNS invoicenumber   VARCHAR2(10)  PATH ''InvoiceNumber'',
10:31:37   8        SellerID VARCHAR2(8)  PATH ''Seller/Identifier'',
10:31:37   9        BuyerID VARCHAR2(8)  PATH ''Buyer/Identifier''
10:31:37  10  ');


Index created.


11:31:14 SQL> create index einvoice_idx_tab_invoicenumber
11:31:14   2  on einvoice_idx_tab (invoicenumber) parallel;


Index created.


11:32:09 SQL> create index einvoice_idx_tab_sellerid
11:32:09   2  on einvoice_idx_tab (sellerid) parallel;


Index created.


11:32:41 SQL> create index einvoice_idx_tab_buyerid
11:32:41   2  on einvoice_idx_tab (buyerid) parallel;


Index created.


11:33:13 SQL> 
11:33:13 SQL> --
11:33:13 SQL> -- Create einvoice_master view
11:33:13 SQL> --
11:33:13 SQL> create or replace view einvoice_master
11:33:13   2  as
11:33:13   3  select m.invoice_no, m.seller_id, m.buyer_id, m.amount
11:33:13   4  from einvoice,
11:33:13   5  xmltable('/A0401/Invoice' passing object_value
11:33:13   6  COLUMNS
11:33:13   7  invoice_no varchar2(10) path 'Main/InvoiceNumber',
11:33:13   8  seller_id varchar2(8) path 'Main/Seller/Identifier',
11:33:13   9  buyer_id varchar2(8) path 'Main/Buyer/Identifier',
11:33:13  10  amount number path 'Amount/SalesAmount') m;


View created.


11:33:13 SQL> 
11:33:13 SQL> --
11:33:13 SQL> -- Create einvoice_details view
11:33:13 SQL> --

20:48:46 SQL> create or replace view einvoice_details
20:48:47   2  as
20:48:47   3  select m.invoice_no, d.*
20:48:47   4  from einvoice,
20:48:47   5  xmltable('/A0401/Invoice' passing object_value
20:48:47   6  COLUMNS
20:48:47   7  invoice_no varchar2(10) path 'Main/InvoiceNumber',
20:48:47   8  details xmltype path 'Details') m,
20:48:47   9  xmltable('/Details/ProductItem' passing m.details
20:48:47  10  COLUMNS
20:48:47  11  item_no number(38) path 'SequenceNumber',
20:48:47  12  quantuty number(38) path 'Quantity',
20:48:47  13  unit_price number(38) path 'UnitPrice',
20:48:47  14  amount varchar2(10) path 'Amount',
20:48:47  15  description varchar2(20) path 'Description') d;


View created.


20:48:48 SQL>

11:33:13 SQL> spool off