eInvoice XMLTYPE tables and query

1. XMLTYPE Table
CREATE TABLE EINVOICE OF SYS.XMLTYPE XMLTYPE STORE AS BINARY XML ( TABLESPACE "FSMS_DATA" CHUNK 8192 ) LOGGING NOCOMPRESS 

2. Using sqlloader to load xml
The best way to load large amount of XML files is through SQL Loader with direct path load.
SQL Loader control file:
load data
infile 'filelist.dat'
APPEND
into table einvoice
xmltype(XMLDATA)
(
filename filler char(120),
XMLDATA lobfile(filename) terminated by eof
)

filelist.dat:
test-000010203-0725.xml
test-000010928-0302.xml
test-000011230-0786.xml
test-000012016-0741.xml
test-000012757-1794.xml
test-000014551-1677.xml

... ...

3. Query sample
The we can query XML content  by using XQuery
SQL> select m.invoice_no
     2  from einvoice,
     3  xmltable('/A0401/Invoice/Main' passing object_value
     4  COLUMNS
     5  invoice_no varchar2(10) path 'InvoiceNumber') m;


Master-Detail Query sample
SQL>

create or replace view einvoice_master_detail_view
as
select m.invoice_no, m.seller_id, m.buyer_id, d.*
from einvoice,
xmltable('/A0401/Invoice' passing object_value
COLUMNS
invoice_no varchar2(10) path 'Main/InvoiceNumber',
seller_id varchar2(8) path 'Main/Seller/Identifier',
buyer_id varchar2(8) path 'Main/Buyer/Identifier',
details xmltype path 'Details'
) m,
xmltable('/Details/ProductItem' passing m.details
COLUMNS
item_no number(38) path 'SequenceNumber',
quantuty number(38) path 'Quantity',
unit_price number(38) path 'UnitPrice',
amount varchar2(10) path 'Amount',
description varchar2(20) path 'Description'
) d;


SQL>

SELECT
XMLQuery('/A0401/Invoice/Main' passing object_value returning content)
from einvoice p
where
XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)


Choose invoice master where invoice number = "AA66279380"
SQL>

SELECT
XMLQuery('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]'  passing object_value returning content)
from einvoice
where
XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)

Choose invoice datails where invoice number = "AA66279380"
SQL>

select
XMLQuery('/A0401/Invoice[Main/InvoiceNumber="AA66279380"]/Details'  passing object_value returning content)
from einvoice
where
XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)





SQL>

select count(*)
from einvoice,
xmltable('/A0401/Invoice' passing object_value
COLUMNS
invoice_no varchar2(10) path 'Main/InvoiceNumber')



SELECT count(*) FROM einvoice
WHERE XMLExists('$p/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE AS "p");



SELECT XMLQuery('$p/A0401/Invoice/Main' PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM einvoice p
WHERE XMLExists('$p/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE AS "p");


SELECT XMLQuery('$p/A0401/Invoice/Main/InvoiceNumber' PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM einvoice p
WHERE XMLExists('$p/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE AS "p")



4. XML Index
Create index to improve query performance

SQL> create index invoice_ix on einvoice(object_value) indextype is XDB.XMLIndex;
Index created.