Using xmlnamesspaces clause in XMLTABLE (Oracle)

Question

I have been trying to get XMLTABLE to work and have been looking at similar posts but am still having problems. The query works but I dont get a value back and I cant see why.

The XML is:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <CstmrPmtStsRpt>
    <GrpHdr>
      <MsgId>185079144aba70c2b</MsgId>
      <CreDtTm>2014-03-10T11:01:18.893Z</CreDtTm>
      <InitgPty>
        <Id>
          <OrgId>
            <BICOrBEI>BARCGB22</BICOrBEI>
          </OrgId>
        </Id>
      </InitgPty>
    </GrpHdr>
    <OrgnlGrpInfAndSts>
      <OrgnlMsgId>AG00411310_1</OrgnlMsgId> 
      <OrgnlMsgNmId>pain.001.001.03</OrgnlMsgNmId>
      <OrgnlNbOfTxs>1</OrgnlNbOfTxs>
      <OrgnlCtrlSum>1234.56</OrgnlCtrlSum>
      <GrpSts>ACCP</GrpSts>
    </OrgnlGrpInfAndSts>
    <OrgnlPmtInfAndSts>
      <OrgnlPmtInfId>AG00411310</OrgnlPmtInfId>
      <OrgnlNbOfTxs>1</OrgnlNbOfTxs>
      <OrgnlCtrlSum>1234.56</OrgnlCtrlSum>
      <PmtInfSts>ACCP</PmtInfSts>
      <TxInfAndSts>
        <OrgnlEndToEndId>AG700060000007</OrgnlEndToEndId>
        <TxSts>ACCP</TxSts>
        <OrgnlTxRef>
          <Amt>
            <InstdAmt Ccy="GBP">1234.56</InstdAmt>
          </Amt>
          <ReqdExctnDt>2014-03-06</ReqdExctnDt>
        </OrgnlTxRef>
      </TxInfAndSts>
    </OrgnlPmtInfAndSts>
  </CstmrPmtStsRpt>
</Document>

I have loaded the XML into an XMLTYPE column in a table I am using for basic testing called doctest (xml is in the column payload). My sql query is below:

select ackd.BICorBIE
from doctest,
XMLTABLE(xmlnamespaces ('urn:iso:std:iso:20022:tech:xsd:pain.002.001.03' as "b"),
'/b:Document' 
PASSING payload
COLUMNS
BICorBIE varchar2(20) PATH 'b:CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/BICOrBEI') ackd;

The query executes without error but the column is NULL.

Answer 1

Your XML declares a namespace but none of the nodes explicitly use it, so your XPath doesn't need to either. So, you can declare it in your statement as the default namespace, not with an identifier:

select ackd.bicorbie
from doctest
cross join xmltable (
  xmlnamespaces (default 'urn:iso:std:iso:20022:tech:xsd:pain.002.001.03'),
  '/Document' 
  passing payload
  columns
    bicorbie varchar2(20) path 'CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/BICOrBEI'
) ackd;

BICORBIE            
--------------------
BARCGB22

You can still declare it as "b" if you want, but then you have to include it as a prefix for every node:

select ackd.bicorbie
from doctest
cross join xmltable(
  xmlnamespaces ('urn:iso:std:iso:20022:tech:xsd:pain.002.001.03' as "b"),
  '/b:Document' 
  passing payload
  columns
    bicorbie varchar2(20) path 'b:CstmrPmtStsRpt/b:GrpHdr/b:InitgPty/b:Id/b:OrgId/b:BICOrBEI'
) ackd;

BICORBIE            
--------------------
BARCGB22

Using a default here is simpler.