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 and up:

apex_json.write (cursor)

The apex_json.write(p_cursor) procedure does a not-so-clever number conversion sometimes.
Whenever it comes across a string that it thinks looks like a number, it will convert it into a number. However, not all numbers are recognized as such, so the result is inconsistent. And any formatting you applied is then lost of course.

I suspect the internal conversion to XML done by apex_json.write is the cause here. See the Oracle documentation on this: "This procedure writes an array with all rows that the cursor returns. Each row is a separate object. If the query contains object type, collection, or cursor columns, the procedure uses write(xmltype) to generate JSON. Otherwise, it uses DBMS_SQL to fetch rows and the write() procedures for the appropriate column data types for output."

A way to fix this is to add a prefix/suffix to the number, and then later remove that - see the "k_spacer replace" in the function fn_apex_json_write_cursor in the code below.

PLS-00801 with json_object and coalesce

On database 18c, when executing the code below you will get a PLS-00801 internal error:
On database 19c and up, this works OK.
A simple fix for 18c is to use nvl instead of coalesce.

json_table case-sensitive path

Turns out that the path in json_table is case-sensitive - I had no idea...

For example: is not the same as
Personally I always use the full syntax with quotes, like this: So I never noticed this until I reformatted some old code using the Beautifier in PL/SQL Developer.
For some reason (I would call it a bug) that Beautifier changes "path Name" into "path name".
Which creates a sneaky little bug in the code, which you may not notice until much later...

More info on the path syntax at https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/function-JSON_TABLE.html#GUID-0172660F-CE29-4765-BF2C-C405BDE8369A.

Code

Page

Identification
Page ID
Name

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Item

Identification
Sequence
Name
Display As
User Interface
Region
Advanced
Warn on Unsaved Changes
Source
Source Used
Source Type
Source value or expression

Package

Package
Name
Source

Package Body

Package Body
Name
Source