DevOps, CI/CD and Automation

Hello all,

Firstly, please let me wish you all a very happy and prosperous 2013!

I have a piece of XML containing namespaces (retrieved from a web service) and I am having difficulty extracting values
(If you're interested this is the webservice: http://api.worldbank.org/countries/FRA/indicators/NY.GDP.MKTP.CD?date=2009)

Here is some test code with two cases -
1) where I leave in the namespace and
2) where I strip out all references to the namespace
Case 1 doesn't work, whereas Case 2 works well.

I would prefer a more elegant solution than simply stripping out the namespace.
I have probably just misunderstood something about how to work with namespaces in PL/SQL.

Do any of you have suggestions about how best to approach this?

Many thanks in advance.
Niall.


set serveroutput on
set define off

DECLARE
v_xml XMLTYPE;
v_clob CLOB;
v_country VARCHAR2(255);
BEGIN
v_clob := '<?xml version="1.0" encoding="utf-8"?>
<wb:data page="1" pages="1" per_page="50" total="1" xmlns:wb="http://www.worldbank.org">
<wb:data>
<wb:indicator id="NY.GDP.MKTP.CD">GDP (current US$)</wb:indicator>
<wb:country id="FR">France</wb:country>
<wb:date>2009</wb:date>
<wb:value>2619685000757.11</wb:value>
<wb:decimal>0</wb:decimal>
</wb:data>
</wb:data>';

v_xml := XMLTYPE(v_clob);
SELECT extractvalue(v_xml,'/data/data[1]/country', 'xmlns:"http://www.worldbank.org/"')
INTO v_country
FROM dual;
dbms_output.put_line(' ');
dbms_output.put_line('*** Case 1');
dbms_output.put_line('*** '||nvl(v_country,'nothing'));
dbms_output.put_line(v_xml.getStringVal());

v_xml := XMLTYPE(replace(v_clob,'wb:')); -- strip out wb:
SELECT extractvalue(v_xml,'/data/data[1]/country', 'xmlns:"http://www.worldbank.org/"')
INTO v_country
FROM dual;
dbms_output.put_line(' ');
dbms_output.put_line('*** Case 2');
dbms_output.put_line('*** '||nvl(v_country,'nothing'));
dbms_output.put_line(v_xml.getStringVal());

END;
/