Database Software

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