extracting a value from XML using SQL using Oracle 11g

Question

I'm trying to extract values from XML that is coming back from a web service call. However, the inclusion of the "xmlns" attribute is causing my query to not work as expected.

This query works as expected, I get "Y" back:

SELECT EXTRACTVALUE(XMLType('<?xml version="1.0" encoding="utf-8"?><string><SAMP_OVERALL>Y</SAMP_OVERALL></string>')
, 
'/string/SAMP_OVERALL') myval
from dual;

Here is the same query but the root tag "string" contains the xmlns attribute. This is how the actual XML comes back. I narrowed down it is the attribute giving me trouble. It returns null:

SELECT EXTRACTVALUE(XMLType('<?xml version="1.0" encoding="utf-8"?><string xmlns="someweburl"><SAMP_OVERALL>Y</SAMP_OVERALL></string>')
, 
'/string/SAMP_OVERALL') myval
from dual;

Why does the inclusion of the xmlns attribute cause this not to return the value of the SAMP_OVERALL tag?

At this point I can't figure out the syntax to pull out the values that I need.

Thanks!

score 1 · Accepted Answer · edited May 23 '17 at 12:23

Try:

SELECT 
  EXTRACTVALUE(XMLType(
     q'[<?xml version="1.0" encoding="utf-8" ?>
        <string xmlns="http://someweburl"><SAMP_OVERALL>Y</SAMP_OVERALL></string>]')
, 
'/*[local-name()="string"]/*[local-name()="SAMP_OVERALL"]') myval
from dual;

This answer explains how to extract values using xpath when default namespace without a prefix is defined :
Getting elements with default namespace (no namespace prefix) using XPath

extracting a value from XML using SQL using Oracle 11g

1 Answers1