Hi,
i have below query. we are able to extract the header, but we need the lines array in the same sql as json output.
DB Version: 12.2.0.1.0
SELECT JSON_OBJECT('hdr_id' VALUE hdr_id
,'prno' VALUE prno
)
FROM XMLTABLE(
'/hdr'
PASSING XMLTYPE('<hdr>
<hdr_id>2238770</hdr_id>
<prno>64922</prno>
<creation_date>2021-09-01</creation_date>
<status>in process</status>
<lines>
<line>
<line_id>2618885</line_id>
<line_num>1</line_num>
<item_description>Test1</item_description>
<uom>each</uom>
<unit_price>400</unit_price>
<quantity>1</quantity>
</line>
<line>
<line_id>2618886</line_id>
<line_num>2</line_num>
<item_description>Test2</item_description>
<uom>each</uom>
<unit_price>555</unit_price>
<quantity>1</quantity>
</line>
</lines>
</hdr>')
COLUMNS
hdr_id VARCHAR2(20) PATH 'hdr_id',
prno VARCHAR2(20) PATH 'prno'
);
Desired Output
{
"hdr_id": "2238770",
"prno": "64922",
"lines": [
{
"line_num": 1,
"item_description": "Test1"
},
{
"line_num": 2,
"item_description": "Test2"
}
]
}