For some reason, the XMLType XPath implementation does not allow scalar results. According to the documentation:
EXTRACT
(XML) is similar to the EXISTSNODE function. It applies a VARCHAR2
XPath string and returns an XMLType instance containing an XML fragment.
Extract
will only return an XMLType. A scalar value would be a function that
returns a varchar2, number or date. What that means is that in an XPath
statement, you could use a function like node() and get an XML fragment
back. But if you try to use name(), which would return the VARCHAR2
name of the designated element, you get an error.
I frequently
need to use name() and local-name(). Count() is another nice function
at times. I decided to write my own extract function. What I really
wanted to do was extend XMLType to allow me to add my own overloaded
Extract. Not gonna happen. XMLType is considered a built-in and not a
UDT so I can't extend it.
More than one way to skin a cat though
so I created my own type that had an XMLType attribute. I created my
own Extract that called an XSLT transform. Now it would be a pain to go
back and forth between a standard XMLType and my home-grown XMLType, so
I put in skeleton code to call the Oracle XMLType. That way I can use my type everywhere I would normally use an XMLType, except mine can return scalar values.
The nice thing about this is that you can add any extensions you think you need.
In
the code below, I don't put every method from the XMLType. I do put an
overloaded Extract() and I show how to add existsNode() and
getStringVal(). Use that example if you would like to extend it further.
I also follow my code up with an anonymous block that shows how to use it.
So here is the type declaration:
Read the rest of this post,
