Oracle's extractValue() Function and XML Namespaces

With the help of Oracle's extractValue() function one can retrieve values from XML documents stored in a database using XPath expressions.

Generally this function works as expected but it gave me a hard time when XML namespaces came into play. As I didn't find much related information on the web I thought a short example might be helpful for others facing the same problem.

Let's take the web service from the video rental shop from my recent post Integrating JAX-WS with XmlBeans as example.

For auditing purposes all requests and responses of the web service might be logged in a database table REQUESTS created as follows:

1 2 3 4 5

CREATE TABLE REQUESTS ( ID NUMBER(10,0) PRIMARY KEY, REQUEST XMLTYPE, RESPONSE XMLTYPE );

In reality, logging would probably be implemented using a message handler, but for demonstration purposes let's simply insert a sample request of the FindMoviesByDirector() operation using SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

INSERT INTO REQUESTS (ID, REQUEST, RESPONSE) VALUES ( 1, '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.gunnarmorling.de/moapa/videorental/types"> <soapenv:Header/> <soapenv:Body> <typ:FindMoviesByDirectorRequest> <Director>Bryan Singer</Director> </typ:FindMoviesByDirectorRequest> </soapenv:Body> </soapenv:Envelope>', '...';

Here we have two namespace aliases declared, soapenv for the SOAP message and typ for the actual message content.

The key for accessing values from this document using extractValue() is the pretty poorly documented optional parameter namespace_string, which can be used to declare any namespaces. This has to happen in the form xmlns:alias="URI", multiple namespaces must be separated by a space character.

Knowing that, it's easy to issue a SQL query which retrieves the director name from the request above. Just make sure to qualify the element names with their namespace alias in the XPath expression:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT req.id, extractValue( req.request, '/soapenv:Envelope/soapenv:Body/typ:FindMoviesByDirectorRequest/Director', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.gunnarmorling.de/moapa/videorental/types"') Title FROM requests req WHERE id = 1 ; ID TITLE ----------------- 1 Bryan Singer