20.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

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