XQuery

Got a small question from Frits if I could help him make some XML data readable in one of the SYS.V_$CELL_% / V$CELL_% views. I have been a bit busy, in between jobs, to try to make some of those XML columns more readable anyway (for myself and others) so…lets have a go at it. …

Continue reading »

Do you read FAQ…?

Somehow I keep people reminding there is a FAQ URL on the XMLDB forum and even then people refuse to read those good examples… Anyway found two great posts I want to share and remember on this, my, web “notepad”. Besides the treewalker example, I tested the examples of those mentioned in the XQuery post on a Oracle 11.2 database.

As far as I could find the treewalker example is part of DOM V2 and not mandatory to implement but I wonder how I can get around the local() stuff, anyway, I will have to investigate a bit further if its just me being a novice in XQuery or that I am missing out on details/info. The XQuery post only demonstrates to me how powerful this extra query language is in an Oracle database and that it is time for me to learn this properly…

The posts that I was referring to:

• Common XQuery mistakes
• The ten most common XSLT programming mistakes

…be aware of the use of the “ (double quote instead single quote), namespaces (indeed apparently always an issue) and using (::) in SQL*Plus… The (::) is needed in SQL*Plus to mark that the “;”  is not seen as direct processing instruction for SQL*Plus, but in this case, is for the XQuery engine.

The headlines follow the ones in the XQuery post…

Something new? Eh? Should you do this? Eh?

In all, probably not, but for me this was a good exercise towards some more updated demo scripting for my “Boost your environment with XMLDB” presentation or hopefully more clearer relabeled Oracle Open World name for the almost same presentation called “Interfacing with Your Database via Oracle XML DB” (S319105). Just up front, there are some issues with the following:

• Why should you do it at all. You should have a good reason doing so…
• It can cause a lot of Physical I/O, at least initially when not cached in the SGA
• Until current versions, AFAIK, it will do a lot of “Pickler Fetching”, serializing in memory, which is very resource intensive (CPU/PGA)
• …and its probably not supported…?

…but it is good fun for a small exercise based on the following OTN Thread: “Error with basic XMLTable“…

Let me show you what I mean.

Via “bfilename” you are able, since a long time, I guess Oracle 9.2 and onwards, to read a file as a BLOB and because an “XMLTYPE” can swallow almost any datatype, you could do the following…