DevOps, CI/CD and Automation

Hi All,

After going thru may of the threads and discussions i have written a query to generate XML from oracle database 11g. I am very close to the result but unable to format the inner result of the xml.

The tag <size_b> is repeating for every <size_d> but i want all the <size_d> tags to come under the <size_b> tag. Could anyone help me out please as i have be unsuccessfully trying for a long time.

Thanks.

Here is the query:
-----------------------------------------------------------------------------------------------------------------
SELECT
XMLELEMENT("order",
(
(
SELECT
XMLAGG
(XMLElement("order_h",
XMLAttributes
(
owner as "owner",
order_no as "order_no",
profoma_no as "profoma_po"
),ORDER_D
)
)
FROM
( SELECT owner,
item_parent,
line_no,
order_no,
diff_1,
diff_3,
profoma_no,
XMLAgg
(
XMLElement("order_d",XMLAttributes
(
owner as "owner",
item_parent as "item_no",
line_no as "line_no",
order_no as "order_no",
diff_1 as "color_code",
diff_3 as "manufacturer"

), SIZE_XML)
) ORDER_D
FROM
(
SELECT assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
model_name,
size_range,
pack_code,
profoma_no,
match_id,
match_01,
XMLAgg
(
XMLElement("size",
XMLAttributes
(
assoc_id as "assoc_id",
match_01 as "match_01",
match_id as "match_id",
pack_code as "pack_code",
size_range as "size_range",
model_name as "model_name",
size_id as "size_id"

), SIZE_B)
) SIZE_XML
FROM
(
SELECT assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
model_name,
size_range,
pack_code,
profoma_no,
priority,
total_pct,
pct_ratio,
match_id,
match_01,
row_no,
XMLAgg
(
XMLElement("Size_b",
XMLAttributes
(
assoc_id as "assoc_id",
row_no as "row_no",
pct_ratio as "pct_ratio",
total_pct as "total_pct",
priority as "priority",
ship_pack as "ship_pack"
),SIZE_D
)
) SIZE_B
FROM
(
SELECT assoc_id,
ctos.order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
ctos.item,
model_name,
size_range,
pack_code,
diff_2,
alloc_qty,
plan_pct_ratio,
row_no,
sort_value,
profoma_no,
priority,
total_pct,
pct_ratio,
match_id,
match_01,
XMLAgg
(
XMLElement("size_d",XMLAttributes
(
assoc_id as "assoc_id",
ship_pack as "ship_pack",
ctos.item as "ctn_sku",
diff_2 as "size_code",
alloc_qty as "alloc_qty",
plan_pct_ratio as "plan_pct_ratio",
row_no as "row_no",
sort_value as "sort_value"
)
)
)SIZE_D
FROM
ct_ordsku_extn ctos,
ct_ordhead_extn ctoh,
item_master im
where ctos.order_no=ctoh.order_no
and ctos.item=im.item

GROUP BY
assoc_id,
ctos.order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
ctos.item,
model_name,
size_range,
pack_code,
diff_2,
alloc_qty,
plan_pct_ratio,
row_no,
sort_value,
profoma_no,
priority,
total_pct,
pct_ratio,
match_id,
match_01

)
GROUP BY
assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
ship_pack,
model_name,
size_range,
pack_code,
profoma_no,
priority,
total_pct,
pct_ratio,
row_no,
match_id,
match_01
)

GROUP BY
assoc_id,
order_no,
owner,
item_parent,
line_no,
diff_1,
diff_3,
size_id,
model_name,
size_range,
pack_code,
profoma_no,
match_id,
match_01
)

GROUP BY
owner,
item_parent,
line_no,
order_no,
diff_1,
diff_3,
profoma_no
)
)
)
).extract('/*')

FROM DUAL;
-----------------------------------------------------------------------------------------------------------------------------------------
The result:

<order>
<order_h owner="GD" order_no="400003" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400003" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
</Size_b>
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
<order_h owner="GD" order_no="400004" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400004" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
</Size_b>
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
</order>
-----------------------------------------------------------------------------------------------------------------------------
Expected result:

<order>
<order_h owner="GD" order_no="400003" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400003" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
<order_h owner="GD" order_no="400004" profoma_po="409536">
<order_d owner="GD" item_no="6534580" line_no="1" order_no="400004" color_code="001" manufacturer="102218">
<size assoc_id="2404" match_id="14" pack_code="BULK" size_range="2" model_name="SIZE SCALE 2" size_id="SIZE SCALE 2">
<Size_b assoc_id="2404" row_no="0" total_pct="100" priority="BULK" ship_pack="BULK">
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526821" size_code="M" alloc_qty="200" plan_pct_ratio="28,2" row_no="0"/>
<size_d assoc_id="2404" ship_pack="BULK" ctn_sku="102526855" size_code="S" alloc_qty="200" plan_pct_ratio="32,4" row_no="0"/>
</Size_b>
</size>
</order_d>
</order_h>
</order>