Thursday, December 30, 2010

Methods to parse XML per Oracle version

One of the items I've noted while hanging out in the XML DB forum or the general XML forum is that many people are still using the old extract and extractValue methods to parse XML via SQL statements.

Starting with 11.2, Oracle has deprecated extract and extractValue. As you can see from the Oracle documentation, Oracle suggest you use XMLQuery in place of extract and either XMLTable or XMLCast/XMLQeury in place of extractValue.

So what method should you use in SQL to parse XML? It depends upon your version of Oracle of course.

Oracle version: 8i - 9.0.x.x
There was no option that I can recall or could find. All the parsing of XML that I've done in 8i was via the xmldom package.

Oracle version: 9.2.x.x - 10.1.x.x
This is were Oracle introduced extract, extractValue and TABLE(XMLSequence(extract())) for dealing with repeating nodes.

Oracle version: 10.2.x.x
Oracle introduced XMLTable as a replacement for the previous methods since it could handle all three methods for extracting data from XML. At that point, Oracle stopped enhancing extract/extractValue in terms of performance and focused on XMLTable. In 10.2.0.1 and .2, XMLTable was implemented via Java and in .3 it was moved into the kernel so performance from .3 onwards should be better than the older 9.2 / 10.1 methods. If not, feel free to open a ticket with Oracle support. Apparently Oracle also introduced XMLQuery as well but I've never heard of many using that in 10.2

Oracle version: 11.1.x.x - 11.2.x.x
Oracle still has XMLTable and XMLQuery as I pointed out above, but also added in XMLCast as a way to cast the output of XMLQuery into a desired datatype.

So when coding, please try to pick the parsing approach that works with the version of Oracle being used. It's good for your job skills and Oracle provides better support for current functionality than deprecated functionality.