2 Replies Latest reply: Nov 15, 2012 8:27 AM by 974415 RSS

    Help with namespace definition in extractvalue()

    974415
      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
          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
            Spot on.

            Thanks very much for your help, much appreciated.