Monday 5 January 2009

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:
for example
You can have multiple declarations separated by white space.

An example is as follows:
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="" ns2="" ns3=""';
l_xml xmltype;
l_clob clob;
l_xml := function_that_gets_an_xmltype_value();
l_clob := l_xml.extract(l_xpath, l_nsmap).getclobval();

Read more about it on page 4.6 of:

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 .


Anonymous said...

You are the ONLY source that I've found that has ANY example of how the namespace string should be constucted, which is pretty much exactly like I expected it would be constructed... only probably is that that syntax is apparently not valid in a select statement as I keep getting a "XPath expression is incorrect" in SQL Developer. Here's the sql query:

select extract(campaign_xml,'//@gname')
from cip_xml_table
where existsNode(campaign_xml, '//cip:Product[@id="P1"],
'cip=""') = 1

Clearly something more then just 'xyz="namespace"' is required but the documenation NEVER USES AN EXAMPLE THAT INCLUDES NAMESPACE. It's like namespace is a dirty secret and they're afraid if they give examples that ANYONE WOULD ACTUALLY USE IN THE REAL WORLD they'd be giving too much away.


Anonymous said...

There's no trick...

where existsnode
( some_xml,
'xmls:ns="http://path/to/namespace/"') = 1

Anonymous said...


Thanks. Indeed the code should just worked. I copy&pasted it from a real working example that is in production for a few years now.

Probably there's something else not exactly correct in the xpath expression.

A good way to debug that is to do a xpath search using JDeveloper on the actual xml document.