we are able to generate the XML structure shown below, where the data is linear with no grouping with respect to the models.
<transaction>
<header>
<trantype>siv</trantype>
<options>ModelMasterEdit</options>
</header>
<detail>
<record>
<Description>test_060420</Description>
<Details>Test Details</Details>
<Canonical>A1863</Canonical>
<Classification>PH</Classification>
<Tree>XM</Tree>
<Manufacturer>Apple</Manufacturer>
<Forecast>Apple Smart Phones</Forecast>
<Category>Cellular Phones</Category>
<SerialCaption>Serial Number</SerialCaption>
<Description>test_060421</Description>
<Details>Test Details</Details>
<Canonical>A1863</Canonical>
<Classification>PH</Classification>
<Tree>XM</Tree>
<Manufacturer>Apple</Manufacturer>
<Forecast>Apple Smart Phones</Forecast>
<Category>Cellular Phones</Category>
<SerialCaption>Serial Number</SerialCaption>
</record>
</detail>
</transaction>
however we need the output in below format by grouping the model attributes with respect to model number
<transaction>
<header>
<trantype>siv</trantype>
<options>ModelMasterEdit</options>
</header>
<detail>
<record>
<Description>test_060420</Description>
<Details>Test Details</Details>
<Canonical>A1863</Canonical>
<Classification>PH</Classification>
<Tree>XM</Tree>
<Manufacturer>Apple</Manufacturer>
<Forecast>Apple Smart Phones</Forecast>
<Category>Cellular Phones</Category>
<SerialCaption>Serial Number</SerialCaption>
</record>
<record>
<Description>test_060421</Description>
<Details>Test Details</Details>
<Canonical>A1863</Canonical>
<Classification>PH</Classification>
<Tree>XM</Tree>
<Manufacturer>Apple</Manufacturer>
<Forecast>Apple Smart Phones</Forecast>
<Category>Cellular Phones</Category>
<SerialCaption>Serial Number</SerialCaption>
</record>
</detail>
</transaction>
we created below SQL query to generate the 1st XML but unable to generate the desired 2nd xml
SELECT XMLELEMENT ("transaction",XMLELEMENT("header", XMLELEMENT ("trantype", 'siv'), XMLELEMENT ("options", 'ModelMasterEdit')),
XMLELEMENT ( "detail",
XMLELEMENT (
"record",
XMLAGG (
XMLELEMENT (evalname(attribute_name), attribute_value)))))
FROM XXCMST_INV_CMT_PRODUCT_TBL i
WHERE soa_instance_id = 64202001
AND source_system = 'EIS'
Table data :