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!

Community · Accepted Answer · 2017-05-23 12:23:47Z

1

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

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Mar 18, 2016 at 21:57

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

extracting a value from XML using SQL using Oracle 11g

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related