How can i convert xml to json in oracle?

Question

If i have

<xml><name>himasnhu</name><age>24</age></xml>  

How can i covert it to

{"name":"himanshu","age":24} .

Thanks.

Answer 1

In Oracle 12.2 you should be able to use:

SELECT JSON_OBJECTAGG( id VALUE text )
FROM   XMLTABLE(
         '/xml/*'
         PASSING XMLTYPE( '<xml><name>himanshu</name></xml>')
         COLUMNS id   VARCHAR2(200) PATH './name()',
                 text VARCHAR2(200) PATH './text()'
       );

In earlier versions you can write a Java function [1] [2] using one of the many Java JSON packages to perform the conversion and then load it into the database using the loadjava utility (or a CREATE JAVA statement) and then use that.

---

Update: Taking data from a table

SELECT JSON_OBJECTAGG( id VALUE text ) AS json
FROM   table_name t
       CROSS APPLY XMLTABLE(
         '/xml/*'
         PASSING XMLTYPE( t.xml )
         COLUMNS id   VARCHAR2(200) PATH './name()',
                 text VARCHAR2(200) PATH './text()'
       );

Which, for the sample data:

CREATE TABLE table_name(xml) AS
  SELECT '<xml><name>himasnhu</name><age>24</age></xml>' FROM DUAL;

Outputs:

JSON
{"name":"himasnhu","age":"24"}

fiddle

Answer 2

You can use the XML to JSON filter to convert an XML document to a JavaScript Object Notation (JSON) document. For details on the mapping conventions used, see:Github- Mapping convention

Configuration

To configure the XML to JSON filter, specify the following fields:

Name:

Enter a suitable name to reflect the role of this filter.

Automatically insert JSON array boundaries:

Select this option to attempt to automatically reconstruct JSON arrays from the incoming XML document. This option is selected by default.

[Note] If the incoming XML document includes the processing instruction, the JSON array is reconstructed regardless of this option setting. If the XML document does not contain , and this option is selected, the filter makes an attempt at guessing what should be part of the array by examining the element names.