Currently on v18.0
Using the JSON_OBJECTAGG function in the below statement:
SELECT JSON_OBJECTAGG( id VALUE text )
FROM XMLTABLE('/xml/place/*' PASSING XMLTYPE( '<xml><id>D073564</id><category>Computer System</category><description>Metropolis</description><place><id>METRO</id><name>Metropolis</name><city>Metropolis</city><address>700 Oracle Way</address>></place><relatedPartyArray><relatedParty><id>PPL000000032042</id><name>Sean Connery</name><role>Agent 007</role></relatedParty><relatedParty><id>PPL000000014442</id><name>Stan Lee</name><role>Agent 008</role></relatedParty></relatedPartyArray><resourceStatus>available</resourceStatus><hostname>d073564</hostname><domain>corp.ads</domain></xml>')
COLUMNS id VARCHAR2(200) PATH './name()',
text VARCHAR2(200) PATH './text()');
I get the following result:
{"id":"D073564",
"category":"Computer System",
"description":"Metropolis",
"place":null,
"relatedPartyArray":null,
"resourceStatus":"available",
"hostname":"d073564",
"domain":"corp.ads"}
This is great, except the "place" and "relatedPartyArray" values are null
If I change the value '/xml/*' to 'xml/place/*', I can get the place value:
{"id":"METRO","name":"Metropolis","city":"Metropolis","address":"700 Oracle Way"}
....but this does not work the same for the array identified as <relatedPartyArray>
Is it possible to get a full json converted extract (with the place and array) from this xml using this function. If so, what changes are needed in the statement?