1 Reply Latest reply: Jan 3, 2013 5:51 PM by Jason_(A_Non) RSS

    Extracting data from an XML document that uses namespaces

    niallmcp
      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;
      /
        • 1. Re: Extracting data from an XML document that uses namespaces
          Jason_(A_Non)
          Your case 1 query should be
          (not tested)
          SELECT extractvalue(v_xml,'/wb:data/wb:data[1]/wb:country', 'xmlns:wb="http://www.worldbank.org/"')
          If the XML is going to be small, you could also do it this way purely in PL/SQL
          [url http://anononxml.blogspot.com/2010/06/xml-parsing-with-namespaces-via-plsql.html]XML Parsing with Namespaces via PL/SQL. Ignore the DOMDocument examples (first two) in there.

          If the XML will be large and you are on 11.1 or greater, then it would be faster to insert the XML into a column in a DB table (could be a global temporary table) where the column is XMLType and the storage is SECUREFILE BINARY (default on 11.2.0.2+). Then you could use XMLTable, like
          [url http://anononxml.blogspot.com/2010/08/xml-parsing-with-namespaces-via.html]XML Parsing with Namespaces via XMLTable