Hi,
For below query, i am able to extract the header, but 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"
}
]
}
You can create a row for each nested line by chaining together XMLTable calls.
This looks something like:
with header as (
select *
from xmltable (
'...' passing xmltype ( .. )
columns
...
nested_element xmltype path '...'
)
)
select *
from header h
left join xmltable (
'...' passing h.nested_element
columns
...
) xt
on 1 = 1
Once you've each line into a row, you can use JSON_arrayagg to combine them back into one document for each header row.
Here's an example to get you started:
with header as (
select *
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',
lines xmltype path 'lines'
)
)
select hdr_id, prno, json_arrayagg ( line_id )
from header h
left join xmltable (
'/lines/line' passing h.lines
columns
line_id int path 'line_id'
) xt
on 1 = 1
group by hdr_id, prno;
/*
HDR_ID PRNO JSON_ARRAYAGG(LINE_ID)
2238770 64922 [2618885,2618886]
*/