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:

Conclusion

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.