Skip to Main Content

XML to JSON

When converting XML to JSON using apex_json procedures/functions like write(), you may run into the problem that arrays with a single entry are incorrectly (or maybe just inconsistently) serialized.
This is explained clearly on this page: https://tapadoo.com/regular-problem-with-xml-to-json-converters/.
Info on the apex_json procedures used can be found at https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#xml-to-json.

I haven't found a solution for this yet, but I thought I'd share my example:
Gives this JSON output: I would have expected (and preferred):
I ran into this when making a template for Apex Office Print, which uses JSON input and markup. The markup for e.g. a {#data_loop}...{/data_loop} tag will fail if the loop only contains one row because of the extra "employee" element (the row will not be shown because the template markup doesn't expect that extra element).

Prettyfier

Some methods to prettify JSON:

From Oracle 12c (note that returning clob is only available from 12.2):
From Oracle 19 (untested, I still use XE 18):