Extracting nodes belonging to different namespaces

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As soon as i posted the previous article on the error caused by a missing namespace string parameter in the call to EXTRACT (or EXTRACTVALUE or UPDATEXML), a subliminal question started buzzing in my head.

What happens if i need to extract node(s) whose XPath expression contains elements belonging to different namespaces?
More precisely, how does the namespace parameter look like in such SQL expression?

The answer, fortunately, was found at the first attempt.
Let's take the same document used previously:

select extractValue(
xml_content,
'/feed/openSearch:totalResults',
'xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/" xmlns="http://www.w3.org/2005/Atom"') val
from xml_documents
where id = 38;

VAL
---
175

As you can see the feed element has a default namespace (no prefix) matched by the blue string and the totalResults element, prefix openSearch, is matched by the green string. Both namespaces appear inside the same string (the famous third parameter of EXTRACT or EXTRACTVALUE), separated by a blank.

I guess one can generalize the namespace syntax as follows:

'xmlns[:prefix]="namespace1" [xmlns[:prefix]="namespace2"] [xmlns[:prefix]=...]'

Specifying the default namespace (sting in purple color in the document above) in the SQL/XML expression is necessary, if you don't do that, you'll get NULL as result:

select extractValue(
xml_content,
'/feed/openSearch:totalResults',
'xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"') val
from xml_documents
where id = 38;

VAL
---
(null)