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:




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.

16 comments:

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?

Thanks,

Mike

Alan said...

Morten,
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.

Thanks,

Alan

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:;)'

Thanks!

Anonymous said...

Error seems to be from the XML_STYLESHEET_PKG



xt



Some others with the error codes suggest that is could possibly be a "namespace issue". APEX version is 3.2.0.00.27. 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?

Regards,
Martin

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?

Regards,
Martin

Martin Köb said...

Hi Morten,

since we switched the Apache webserver to SSL the XML tree is not displayed in the browser.
The IE 11 gives me the follwoing errors, infos:

DOM7011: Der Code auf dieser Seite hat die Zwischenspeicherung für das Vor- und Zurücknavigieren deaktiviert.

HTML1500: Die Markierung kann nicht selbstschließend sein. Verwenden Sie eine explizite schließende Markierung.

SCRIPT1002: Syntaxfehler

HTML1521: Unerwartete "body"-Markierung oder Dateiende. Alle geöffneten Elemente sollten vor dem Ende des Dokuments geschlossen werden.

Do you have a hint or a tipp how to solve this problem?

Regards,
Martin

Martin Köb said...

Hi Morten,

the problem is not based on SSL.
Could the reason be that we have IE 11?

Regards,
Martin

Martin Köb said...

Hi Morten,

the problem seems to be in the transformation of the XML via stylesheet.
The problem occurs @IE 10 and 11.

Regards,
Martin

Martin Köb said...

Hi Morten,

do you have an idea how we can solve the problem?

Regards,
Martin

Anonymous said...

Hi,
iam not able to get this xml package any more? did the location change? can someone please provide me with the link.

thanks.

koloo kumo said...

Hi Morten
Iam still having problems with the package especially when the xml data is huge like more than 4000 characters. Do you have any recommendations for large xml datas?

Morten Braten said...

@Anonymous: The package described in this blog post is part of the Alexandria Utility Library for PL/SQL which is currently located at https://github.com/mortenbra/alexandria-plsql-utils

- Morten

Morten Braten said...

@Koolo: As long as you use clob, not varchar2, there should not be any problems with large XML files. Please provide actual code and/or error messages if you need more specific advice.

- Morten