EBS : How to read XML File using SQL Query: Oracle APPS

 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       

                                 ;