33.36 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 33-50 TO_XMLTYPE_SQL Function Parameters
Parameter | Description |
---|---|
|
The JSON source ( |
|
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()'
);
Parent topic: APEX_JSON