For some reason, the XMLType XPath implementation does not allow scalar results. According to the documentation:


(XML) is similar to the EXISTSNODE function. It applies a VARCHAR2

XPath string and returns an XMLType instance containing an XML fragment.


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.


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,