SQL & PL/SQL

Hello all,

Thanks in advance.

I am using XMLTABLE function for parsing an XML which carries relational data to be inserted in some 14 tables.

This is a sample of the XMLTABLE SELECT query I am using:

SELECT Inch.InterchangeRefno, Inch.FileName, Txns.TransactionSeq

            FROM XMLTABLE (

                    '/BookingBL'

                    PASSING XMLTYPE (BFILENAME ('XMLREAD', 'Sample_file.xml'),

                                     NLS_CHARSET_ID ('UTF8'))

                    COLUMNS Interchange XMLTYPE PATH 'Interchange',

                            Transactions XMLTYPE PATH 'Transactions') Booking,

                 XMLTABLE (

                    '/Interchange'

                    PASSING Booking.Interchange

                    COLUMNS InterchangeRefno VARCHAR2 (100) PATH 'InterchangeRefno',

                            FileName VARCHAR2 (100) PATH 'FileName') Inch,

                 XMLTABLE ('/Transactions/Transaction'

                           PASSING Booking.Transactions

                           COLUMNS TransactionSeq NUMBER PATH '@SeqNo') Txns;

In this query, If the  '/Interchange' tag is not coming in the XML (since the XML is formed based on a incoming EDI text data, some data may come or not), then the entire SELECT query is returning no data.

My requirement here is, even if tags which are specified in the XMLTABLE('tag'   PASSING..... clause is absent, the query should return the data from the previous parsing alone (in my case interchange detail alone).

I browsed a lot for a solution, but I couldn't find the solution for this. i.e. optional tags in XMLTABLE PASSING clause.

Please light me in this.

Thanks again.