1 Reply Latest reply: Oct 24, 2013 1:09 AM by odie_63 RSS

    Oracle XMLTable function no rows returned

    hrishy
      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

          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.