id="info"

Developer

Mini-Tip #8 - Extending XMLType to Access Scalar XPath Functions

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,

Editor's Picks