I have following xml which I want to generate using oracle.
<Component ID="600564" asmid="1" def="FactSheet" type="Document">
<Profile>
<Port_ID>182</Port_ID>
<Inception_Date>31 Dec 1998</Inception_Date>
</Profile>
<AverageAnnualReturns>
<TableData>
<Item>
<ClassID>33</ClassID>
<ClassName>A1USD</ClassName>
<Superscript>2</Superscript>
<Superscript_TCE/>
</Item>
<Item>
<ClassID>45</ClassID>
<ClassName>I1EUR</ClassName>
<Superscript>1,2</Superscript>
<Superscript_TCE/>
</Item>
</TableData>
<SuppressLifeColumn>Yes</SuppressLifeColumn>
<SuppressBenchColumn10yr>No</SuppressBenchColumn10yr>
<SuppressBenchColumn5yr>No</SuppressBenchColumn5yr>
<SuppressBenchColumn3yr>No</SuppressBenchColumn3yr>
<SuppressBenchColumn1yr>No</SuppressBenchColumn1yr>
</AverageAnnualReturns>
</Component>
Query for profile as follow
SELECT PCR.PORT_ID,
to_char(p.port_start_dt, 'dd Mon yyyy') as INCEPTION_DATE
FROM edp_admin.PORT_CLASS_REPORT PCR
JOIN edp_admin.PORTFOLIO p on (PCR.PORT_ID = P.PORT_ID and PCR.CLASS_ID = P.BASE_CLASS_ID)
where PCR.PORT_ID = 182 AND PCR.REPORT_ID = 472
Query for AverageAnnualReturn is as follow
SELECT distinct MPR.PORT_ID,
MPR.CLASS_ID,
MPR.CLASS_NAME,
PCR.VALUE_NUM3 as RANK,
NVL (NC.SUPERSCRIPT_TEXT,' ') as SUPERSCRIPT_TEXT
FROM EDP_ADMIN.SYSTEM_VALUE PCR
LEFT JOIN EDP_ADMIN.MPR_MIL_VIEW MPR ON (PCR.VALUE_NUM1 = MPR.PORT_ID AND PCR.VALUE_NUM2 = MPR.CLASS_ID AND MPR.CLASSIF_ID = 206)
LEFT JOIN EDP_ADMIN.PORT_CLASS_YIELD PCY ON (PCR.VALUE_NUM1 = PCY.PORT_ID AND PCR.VALUE_NUM2 = PCY.CLASS_ID AND MPR.PROFILE_DATE = PCY.PORT_CLASS_YIELD_DT AND PCY.YIELD_ID = 6)
LEFT JOIN EDP_ADMIN.PORTFOLIO_CLASS PC ON (PCR.VALUE_NUM1 = PC.PORT_ID AND PCR.VALUE_NUM2 = PC.CLASS_ID)
LEFT JOIN EDP_ADMIN.PORT_CLASS_MKT_PRICE PCMP ON (PCR.VALUE_NUM1 = PCMP.PORT_ID AND PCR.VALUE_NUM2 = PCMP.CLASS_ID AND PCMP.MKT_PRICE_ID = 12 AND MPR.PROFILE_DATE = PCMP.PORT_CLA_MKT_PRICE_PER_END_DT)
LEFT JOIN (SELECT DISTINCT R1.PORT_ID, R1.CLASS_ID, R1.PORT_CLASS_EXP_RAT_VALUE, R1.PORT_CLASS_EXP_RAT_DT FROM edp_admin.Port_Class_Expense_Ratio R1 INNER JOIN (SELECT DISTINCT PORT_ID, CLASS_ID, MAX(PORT_CLASS_EXP_RAT_DT) as MONTH_END_DATE FROM EDP_ADMIN.PORT_CLASS_EXPENSE_RATIO GROUP BY PORT_ID, CLASS_ID ) R2 ON (R1.PORT_ID = R2.PORT_ID AND R1.CLASS_ID = R2.CLASS_ID AND R1.PORT_CLASS_EXP_RAT_DT = R2.MONTH_END_DATE) ) PCER ON PCR.VALUE_NUM1 = PCER.PORT_ID and PCR.VALUE_NUM2 = PCER.CLASS_ID
LEFT JOIN EDP_ADMIN.FN_MIL_FS_CONCAT_NOTES_VIEW NC ON NC.PORT_ID = PCR.VALUE_NUM1 AND NC.CLASS_ID = PCR.VALUE_NUM2 AND NC.HYPO_TYPE = PCR.VALUE_CHAR1
WHERE PCR.CODE = 'MIL_FS_PERF_CONFIG' AND PCR.VALUE_NUM1 IN (182) AND PCR.VALUE_CHAR1 = 'UK' ORDER BY RANK
I tried using oracle sql functions for generating xml like below
select dbms_xmlquery.getxml(sql for profile) from dual
but it generates two extra xml nodes rowset and row like below
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<PORT_ID>182</PORT_ID>
<INCEPTION_DATE>31 Dec 1998</INCEPTION_DATE>
</ROW>
</ROWSET>
Other way was
select xmlelement("Component",xmlattributes('600564' as ID,
'1' as asmid,
'FactSheet' as def,
'Document' as type
),
xmlagg(
xmlelement("Profile")
)
)from dual
It generates structure as below
<Component ID="600564" ASMID="1" DEF="FactSheet" TYPE="Document"><Profile></Profile></Component>
BUt I am not geeting how do I populate data under profile node then averageannualreturns