EBS : How to read XML File using SQL Query: Oracle APPS
Preparing a Nested Query to read XML FILE from Appl Server.
select Hdrxml.TRX_ID, Hdrxml.BILL_TO_NAME, Hdrxml.TRX_NUMBER,Hdrxml.G_LINE,linexml.*
from
(SELECT XMLTYPE(bfilename('XX_AR_INV_OUT', 'o87864385Copy.out'), nls_charset_id('UTF8')) xml_data
FROM dual) a, xmltable('/XX_AR_INV_XML_GEN/LIST_G_INV/G_INV' passing a.xml_data
columns TRX_ID NUMBER path 'TRX_ID',
BILL_TO_NAME VARCHAR2(1000) path 'BILL_TO_NAME',
TRX_NUMBER VARCHAR2(1000) path 'TRX_NUMBER' ,
G_LINE XMLTYPE PATH 'LIST_G_LINE/G_LINE'
) Hdrxml,
xmltable('/G_LINE' passing Hdrxml.G_LINE
columns LINE_NUMBER NUMBER path 'LINE_NUMBER',
ITEM_NUMBER VARCHAR2(1000) path 'ITEM_NUMBER',
QUANTITY_SHIPPED VARCHAR2(1000) path 'QUANTITY_SHIPPED',
UNIT_PRICE VARCHAR2(1000) path 'UNIT_PRICE',
UOM_CODE VARCHAR2(1000) path 'UOM_CODE'
) linexml
;
