How to create a nested json object from xml data in oracle?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DECLARE
  xml_data XMLTYPE := XMLTYPE('<data>
                              <person>
                                <name>John Doe</name>
                                <age>30</age>
                                <address>
                                  <street>123 Main St</street>
                                  <city>New York</city>
                                </address>
                              </person>
                            </data>');
BEGIN
  -- your SQL code here
END;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT
  JSON_OBJECT(
    'person' VALUE JSON_OBJECT(
      'name' VALUE name,
      'age' VALUE age,
      'address' VALUE JSON_OBJECT(
        'street' VALUE street,
        'city' VALUE city
      )
    )
  ) AS nested_json_object
FROM
  XMLTABLE(
    '/data/person'
    PASSING xml_data
    COLUMNS
      name VARCHAR2(50) PATH 'name',
      age NUMBER PATH 'age',
      street VARCHAR2(50) PATH 'address/street',
      city VARCHAR2(50) PATH 'address/city'
  );