This discussion is archived
2 Replies Latest reply: Nov 15, 2012 6:27 AM by 974415 RSS

Help with namespace definition in extractvalue()

974415 Newbie
Currently Being Moderated
Hi

I am using PL/SQL on Oracle 11.2.0.3.0 Enterprise Edition to call a webservice.

I have a successful response in a variable of type xmltype.

I am trying to extract the md5sum value but I cannot seem to specify the correct namespace for the extractvalue function.

The XML response is
<GetFileDetailsResponse xmlns="http://10.1.0.1">
<GetFileDetailsResult>
<FileDetails xmlns="">
<md5sum>908655930beaa7ea3bca7a98adabfcf4</md5sum>
<FileSize>50929664</FileSize>
<FileLastModified>2011-05-03T12:33:16+01:00</FileLastModified>
</FileDetails>
</GetFileDetailsResult>
</GetFileDetailsResponse>

Simplfying this down I have constructed the following select statement

select extractvalue(xmltype.createxml('<GetFileDetailsResponse xmlns="http://10.1.0.1">
<GetFileDetailsResult>
<FileDetails xmlns="">
<md5sum>908655930beaa7ea3bca7a98adabfcf4</md5sum>
<FileSize>50929664</FileSize>
<FileLastModified>2011-05-03T12:33:16+01:00</FileLastModified>
</FileDetails>
</GetFileDetailsResult>
</GetFileDetailsResponse>'),'/GetFileDetailsResponse/GetFileDetailsResult/FileDetails/md5sum','xmlns="http://10.1.0.1" xmlns="" ') md5 from dual;

This returns null

However, if I remove the two namepsace references from the xml -

select extractvalue(xmltype.createxml('<GetFileDetailsResponse>
<GetFileDetailsResult>
<FileDetails>
<md5sum>908655930beaa7ea3bca7a98adabfcf4</md5sum>
<FileSize>50929664</FileSize>
<FileLastModified>2011-05-03T12:33:16+01:00</FileLastModified>
</FileDetails>
</GetFileDetailsResult>
</GetFileDetailsResponse>'),'/GetFileDetailsResponse/GetFileDetailsResult/FileDetails/md5sum') md5 from dual;

I get 908655930beaa7ea3bca7a98adabfcf4 - the answer I want. Looks like I am specifying the wrong namespace in the extractvalue statement.

Can anyone suggest the correct extractvalue statement I should be using. I've probably looked at this for too long - I'm must be missing the obvious.

Many thanks for taking the time to read this,

Cheers,

Derek
  • 1. Re: Help with namespace definition in extractvalue()
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Since the XPath expression references both elements in a namespace and elements in no namespace, you have to declare a prefix for the default namespace :
    SQL> select extractvalue(
      2    xmltype.createxml('<GetFileDetailsResponse xmlns="http://10.1.0.1">
      3  <GetFileDetailsResult>
      4  <FileDetails xmlns="">
      5  <md5sum>908655930beaa7ea3bca7a98adabfcf4</md5sum>
      6  <FileSize>50929664</FileSize>
      7  <FileLastModified>2011-05-03T12:33:16+01:00</FileLastModified>
      8  </FileDetails>
      9  </GetFileDetailsResult>
     10  </GetFileDetailsResponse>')
     11  , '/ns0:GetFileDetailsResponse/ns0:GetFileDetailsResult/FileDetails/md5sum'
     12  , 'xmlns:ns0="http://10.1.0.1"'
     13  ) md5
     14  from dual;
     
    MD5
    --------------------------------------------------------------------------------
    908655930beaa7ea3bca7a98adabfcf4
     
    But... extractValue is deprecated in your version.
    You should now use XQuery functions :
    SQL> DECLARE
      2  
      3    response xmltype := xmltype(
      4    '<GetFileDetailsResponse xmlns="http://10.1.0.1">
      5  <GetFileDetailsResult>
      6  <FileDetails xmlns="">
      7  <md5sum>908655930beaa7ea3bca7a98adabfcf4</md5sum>
      8  <FileSize>50929664</FileSize>
      9  <FileLastModified>2011-05-03T12:33:16+01:00</FileLastModified>
     10  </FileDetails>
     11  </GetFileDetailsResult>
     12  </GetFileDetailsResponse>');
     13  
     14    result  varchar2(80);
     15  
     16  BEGIN
     17  
     18    select /*+ no_xml_query_rewrite */
     19           xmlcast(
     20             xmlquery(
     21             'declare namespace ns0 = "http://10.1.0.1"; (: :)
     22             /ns0:GetFileDetailsResponse/ns0:GetFileDetailsResult/FileDetails/md5sum'
     23             passing response
     24             returning content
     25             )
     26            as varchar2(80)
     27          )
     28    into result
     29    from dual
     30    ;
     31  
     32    dbms_output.put_line(result);
     33  
     34  END;
     35  /
     
    908655930beaa7ea3bca7a98adabfcf4
     
    PL/SQL procedure successfully completed
     
  • 2. Re: Help with namespace definition in extractvalue()
    974415 Newbie
    Currently Being Moderated
    Spot on.

    Thanks very much for your help, much appreciated.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points