This discussion is archived
1 Reply Latest reply: Oct 23, 2013 11:09 PM by odie_63 RSS

Oracle XMLTable function no rows returned

hrishy Newbie
Currently Being Moderated
I have a following xml document stored in XML type table
<?xml version="1.0" encoding="utf-8"?>
<Document xmlns="urn:iso:std:iso:200:tech:xsd:101" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FIToFICstmrCdtTrf xmlns="urn:iso:std:iso:200:tech:xsd:101">
                            <CreditTx>
                <PaymentID>
                    <InstrumentId>AAB000001</InstrumentId>
                    <Id>4730 2013-10-23 AAB000001</Id>
                    <TranxId>BULKTTXTDAAB000001</TranxId>
                </PaymentID>
            </CreditTx>
        </FIToFICstmrCdtTrf>
</Document>

 

And i am trying to select

I am on Oracle Version 11.2.0.3SELECT payments.txid,
payments.endtoendid,
payments. instrid
FROM payment_load_xml,
XMLTable('for $i in /Document/FIToFICstmrCdtTrf/CdtTrfTxInf/PmtId
return $i'
PASSING OBJECT_VALUE
COLUMNS
instrid VARCHAR2(20) PATH 'InstrId' ,
endtoendid VARCHAR2(20) PATH 'EndToEndId' ,
txid VARCHAR2(20) PATH 'TxId'
)payments

 

I am getting no rows returned any ideas please

  • 1. Re: Oracle XMLTable function no rows returned
    odie_63 Guru
    Currently Being Moderated

    The document has a default namespace, so you have to declare it :

    SELECT payments.txid, 

           payments.endtoendid, 

           payments. instrid 

    FROM payment_load_xml, 

         XMLTable(

          XMLNamespaces(default 'urn:iso:std:iso:200:tech:xsd:101') ,

           'for $i in /Document/FIToFICstmrCdtTrf/CdtTrfTxInf/PmtId 

            return $i' 

           PASSING OBJECT_VALUE 

           COLUMNS 

             instrid    VARCHAR2(20) PATH 'InstrId' , 

             endtoendid VARCHAR2(20) PATH 'EndToEndId' , 

             txid       VARCHAR2(20) PATH 'TxId' 

    ) payments ;

     

    and additionally, your paths don't point to anything in the sample document.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points