Tuesday, February 1, 2011

Display any XML as clickable tree using PL/SQL (and Apex)

I don't have too many good things to say about IE, but I do like how it displays XML files. Chrome's handling of XML files (or total lack of it) sucks. But IE (and Firefox) gives you a nice tree structure which allows you to click on the various nodes to expand or collapse them.

This is actually implemented as an XSL stylesheet that is "hardcoded" into IE, and it is possible to extract it.

Using the default IE stylesheet for XML in your own applications

Here's how we can use this XSL stylesheet in our own PL/SQL and/or Apex applications. Let's pretend we receive product information in XML format from a business partner. We parse the XML file and put the information into our product database. When something goes wrong it's helpful to look at the raw XML, so we store that in the database as well (typically in a CLOB or XMLTYPE column).

Using Apex, we build a web page to look at the logs, including the XML content. We'd like a nice clickable treeview instead of the raw XML.

Here's where our XSL stylesheet (extracted from IE) comes in. That stylesheet can be found in the PL/SQL package called XML_STYLESHEET_PKG which is part of Alexandria, the PL/SQL Utility Library. Download the library and install it, then continue with these instructions.

In Apex, create a blank page (I'll be using page 6) and put an HTML Region on it. Then put a Textarea item (called P6_XML_INPUT) in this region. This will simulate our XML product file, although in reality you would of course pull this from the database or a file on disk. Also add a button to submit the page (back to itself).

Next, add a PL/SQL Region to the page, and put the following as the region source:

Run the page, paste some (valid) XML into the text area, submit the page, and voila! The PL/SQL region should show the data nicely formatted as a clickable tree.

Another example

This "clickable tree" can also be useful to show data from regular tables or queries.

You can easily create an XML representation of a query by using a REF CURSOR and passing it to the XMLTYPE type.

For example, create a PL/SQL Region and use this as the source:

Run the page, and the output should look something like this:


Using some handy utility packages from the PL/SQL Utility Library and a few lines of PL/SQL code, you can display any XML document as a clickable tree.


Anonymous said...

Hi Morten,
This works really nicely, is it possible to default the output of the XML to have each node expanded rather than collapsed?



Alan said...

This is great, but I have the same question as Mike above...in IE is it possible to open the XML output fully expanded? This seems like default behaviour in Chrome, the whole tree is expanded but in IE it opens just showing ROWSET.



Anonymous said...

Hello, I think I am pretty close, but I am receiving following Error when xml is submitted:

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00601: Invalid token in: 'starts-with(name(),;xsl:;)'


Anonymous said...

Error seems to be from the XML_STYLESHEET_PKG


Some others with the error codes suggest that is could possibly be a "namespace issue". APEX version is Thanks

Anonymous said...

Nicely working with IE. But unfortunatelly it is not working with Mozilla Firefox (24 ESR).

Anonymous said...

Hi Morten,

i can't find the package owa_util_pkg in the database. Can you give me a hint please?


Morten Braten said...

@Martin: The owa_util_pkg package is part of the Alexandria PL/SQL Utility Library (https://github.com/mortenbra/alexandria-plsql-utils), just like the xml_stylesheet_pkg itself.

- Morten

Anonymous said...

Hi Morten,

thanks i found it and it works fine :-)
Did you found a solution for the possibility to expand each node?