21.33 TO_XMLTYPE_SQL Function

This function parses a JSON-formatted varchar2 or CLOB and converts it to an xmltype. This function overload has the p_strict parameter as VARCHAR2 in order to allow invoking from within a SQL query and having JSON parsing in LAX mode.

Syntax

function to_xmltype_sql (
    p_source   IN VARCHAR2,
    p_strict   IN BOOLEAN DEFAULT 'Y' )
RETURN sys.xmltype;

function to_xmltype_sql (
    p_source   IN CLOB,
    p_strict   IN BOOLEAN DEFAULT 'Y' )
RETURN sys.xmltype;

Parameters

Table 21-42 TO_XMLTYPE_SQL Function Parameters

Parameter	Description
p_source	The JSON source (VARCHAR2 or CLOB)
p_strict	If Y (default), enforce strict JSON rules

Returns

An xmltype representation of the json data

Example

This example SQL query converts JSON to XMLTYPE and uses the XMLTABLE SQL function to extract data. The p_strict argument is set to N , so the JSON can successfully be parsed in lax mode, although the items attribute is not enquoted.

select
    attr_1
from
    xmltable(
    '/json/items/row'
    passing apex_json.to_xmltype_sql( '{ items: [ 1, 2, { "foo": true } ] }', p_strict => 'N' )
    columns
    attr_1 varchar2(20) path 'foo/text()'
 );