Search code examples
sqloracle-databasexml-namespacesxmltypexmltable

Reading XML Namespace using Oracle SQL

My XML Looks like below 

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>  
<wfm:Statement xmlns:wfm="http://example.org/sample/xsd/sampleStatement/2013/05" xmlns:wfmMerchant="http://www.eds.com/sample/xsd/wfmMerchant/2012/03"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <wfm:StatementParameters>  
    <wfmMerchant:HierarchyCd>012-12-002-107-050</wfmMerchant:HierarchyCd>   
  </wfm:StatementParameters>  
  <StatementAmount>27.140</StatementAmount>  
</wfm:Statement>

I am trying to get the value of StatementAmount tag using Oracle query like below

select MS.MERCHANT,MS.CHAIN_HIERARCHY_CD,MS.CYCLE_DATE, X.StatementAmount
FROM CHAIN_STATMNT_HIST_XML MS  
CROSS JOIN XMLTABLE(XMLNAMESPACES('http://example.org/sample/xsd/sampleStatement/2013/05' AS "wfm", 'http://www.eds.com/sample/xsd/wfmMerchant/2012/03' as wfmmerchant
     default 'http://www.w3.org/2001/XMLSchema-instance')
     ,'/wfm:Statement/StatementAmount' passing xmltype(MS.XML_REPORT) 
     columns StatementAmount varchar(18) path '.')X

But, I am always getting NULL. I can able to successfully retrieve Hierarchy value from the XML which has namespace. But StatementAmount tag doesn't have any namespace and I have trouble retrieving it.

Can someone help with this issue ?

Solution

  • Your default namespace declaration seems to be causing the problem; without that (and ignoring wfmMerchant):

    -- CTE for sample data
with CHAIN_STATMNT_HIST_XML (merchant, chain_hierarchy_cd, cycle_date, XML_REPORT) as (
  select 1, 2, sysdate, '<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<wfm:Statement xmlns:wfm="http://example.org/sample/xsd/sampleStatement/2013/05" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<wfm:StatementParameters>
<!-- excluding this as namespace not provided -->
<!-- <wfmMerchant:HierarchyCd>012-12-002-107-050</wfmMerchant:HierarchyCd> -->
</wfm:StatementParameters>
<StatementAmount>27.140</StatementAmount>
</wfm:Statement>' from dual
)
-- actual query
select MS.MERCHANT,MS.CHAIN_HIERARCHY_CD,MS.CYCLE_DATE, X.StatementAmount
FROM CHAIN_STATMNT_HIST_XML MS  
CROSS JOIN XMLTABLE(
  XMLNAMESPACES('http://example.org/sample/xsd/sampleStatement/2013/05' AS "wfm"),
  '/wfm:Statement/StatementAmount' passing xmltype(MS.XML_REPORT)
  columns StatementAmount varchar(18) path '.'
) X
/

  MERCHANT CHAIN_HIERARCHY_CD CYCLE_DATE STATEMENTAMOUNT   
---------- ------------------ ---------- ------------------
         1                  2 2018-09-04 27.140

    I'm not sure why you would use varchar2(18) as the datatype rather than number; and if there is only one statement amount per statement you could do:

    select MS.MERCHANT,MS.CHAIN_HIERARCHY_CD,MS.CYCLE_DATE, X.StatementAmount
FROM CHAIN_STATMNT_HIST_XML MS  
CROSS JOIN XMLTABLE(
  XMLNAMESPACES('http://example.org/sample/xsd/sampleStatement/2013/05' AS "wfm"),
  '/wfm:Statement' passing xmltype(MS.XML_REPORT)
  columns StatementAmount number path 'StatementAmount'
) X