Xpath expressions with namespaces on XMLType

I used to use XMLTypes with xml where I tend to prevent the usage of namespaces. Namespaces make things difficult where they are often unneeded. That is I experienced that it is hard. An expression like '/level1/level2/level3' is much simpeler then '/ns1:level1/ns2:level2/ns3:level3'. Especially if you don't know how to give in the particular namespaces.

Lately I wanted to store the results of a BPEL Process in the database to be able to query parts from that in a later process. Actually I processed several files in the one process and wanted to query the statuses in the later process again. If you work with BPEL you cannot avoid using namespaces. I also wanted to put the results in an XMLtype as is, since it prevented me to create a complete datamodel. A simple xmltype-table suffices.

I then created a pl/sql function with the filename as a parameter that fetched the right row in the results table and gave back the xmltype column in which the status of that file was stored. But then, how to query it with XPath?

I knew the xmltype.extract() function. But what next? You can avoid namespaces by using the local-name() xpath function, but then it is a little hard to get the value of the particular file.

Luckily the extract() function has another parameter, the namespace string:

extract(XMLType_instance IN XMLType,
XPath_string IN VARCHAR2,
namespace_string In VARCHAR2 := NULL) RETURN XMLType;

This namespace string can contain the namespace declarations to be used in an xpath expression. The namespace declarations look like:
namespace-shortage=uri
for example
ns1="http://www.example.org/namespace1"
You can have multiple declarations separated by white space.

An example is as follows:

declare
xp_no_data_found exception;
pragma exception_init(xp_no_data_found, -30625);
l_xpath varchar2(32767) := '/ns1:level1/ns2:level2/ns3:level3';
l_nsmap varchar2(32767) := 'ns1="http://www.example.org/namespace1" ns2="http://www.example.org/namespace2" ns3="http://www.example.org/namespace3"';
l_xml xmltype;
l_clob clob;
begin
l_xml := function_that_gets_an_xmltype_value();
l_clob := l_xml.extract(l_xpath, l_nsmap).getclobval();
end;

Read more about it on page 4.6 of: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259.pdf.

Mark also that there are actually two functions: extract() and extractvalue(). Extract() returns an xmltype, that can be 'sub-queried'. The extractvalue() function returns the datatype of the variable that the value is assigned to. With the xmltype functions getstringval(), getclobval(), etc. you can also get the particular value of a node of the xmltype. However, there is a slight difference between the result of the getstringval() and correspondingfunctions and Extractvalue. And that is that the Extractvalue returns the unescaped value of the node (the encoding entities are unescaped), while getstringval() returns the value with the entity encodings intact .