Reading XML Namespace using Oracle SQL

Question

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 ?

Not being able to copy the xml as text stops me creating an answer for you. — Rene
– Rene, Commented Sep 4, 2018 at 7:33
Your XML doesn't have a namespace declaration for wfmMerchant? And why are you declaring a default namespace in the XMLTable call? — Alex Poole
– Alex Poole, Commented Sep 4, 2018 at 8:58
wfmMerchant does have namespace. There is no trouble retrieving it. I haven't added it in the question as it has some sensitive info. — Venkata Thulasi B
– Venkata Thulasi B, Commented Sep 4, 2018 at 9:01

Alex Poole · Accepted Answer · 2018-09-04 09:07:51Z

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

Collectives™ on Stack Overflow

Reading XML Namespace using Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related