Hi, new to xml creating from plsql, sorry
I need to create xml section that includes 2 nested elements within outer tag. Each nested element can have 0 to multiple records.
Example:
<Research_Report transaction="insert"><app_num>1</app_num><report_type>FINAL</report_type><report_num>1</report_num>
<RRS_Category><category_type_cd>RARA</category_type_cd><category_cd>POP</category_cd><priority_order>1</priority_order></RRS_Category>
<RRS_Category><category_type_cd>RARA</category_type_cd><category_cd>BEER</category_cd><priority_order>2</priority_order></RRS_Category>
<RRS_Category><category_type_cd>RCLS</category_type_cd><category_cd>21</category_cd><priority_order>1</priority_order></RRS_Category>
<RRS_Category><category_type_cd>RCLS</category_type_cd><category_cd>77</category_cd><priority_order>2</priority_order></RRS_Category>
<RRS_Category><category_type_cd>INST</category_type_cd><category_cd>I</category_cd><priority_order>1</priority_order></RRS_Category>
<RRS_Keyword><keyword>TEST</keyword></RRS_Keyword>
<RRS_Keyword><keyword>HI</keyword></RRS_Keyword>
</Research>
This is what I have so far
SELECT
XMLelement("Research",
XMLattributes ('insert' AS "transaction"),
XMLforest(
rep.app_num AS "app_num"
,rep.research_report_type AS "report_type"
,rep.research_report_num AS "report_num"
,rep.prc_cd AS "prc_cd"
,XMLagg(XMLForest( afc1.category_type_cd AS "category_type_cd"
,afc1.code_value AS "category_cd"
,afc1.priority_order AS "priority_order"
) /*order by afc1.catergory_type_cd, afc1.priority_order */ )AS "RRS_Category"
,XMLagg(XMLForest( afc2.code_value AS "keyword"
) ) AS "RRS_Keyword"
)
) fragment
FROM research_report rep
JOIN mrc_funding mf ON rep.mrc_funding_id = mf.mrc_funding_id
LEFT OUTER JOIN application_funding_category afc1 ON (afc1.app_num = rep.app_num AND afc1.category_type_cd <> 'KEYW')
LEFT OUTER JOIN application_funding_category afc2 ON (afc2.app_num = rep.app_num AND afc2.category_type_cd = 'KEYW')
WHERE rep.research_report_xml_flag = 'F'
AND rep.research_report_action_cd = 'I'
group by rep.app_num,rep.research_report_type,rep.research_report_num,rep.prc_cd
ORDER BY rep.app_num, rep.research_report_type, rep.research_report_num;
2 problems:
#1 I get a Cartesian join between rrs_category and rrs_keyword, query works if I comment out one section
#2 I need to order by for results in rrs_category based on: order by afc1.catergory_type_cd, afc1.priority_order
Any help would be very much appreciated, thanks
Dave