Hi ALL,
Below is the sample xml query used to create XML from database tables. When there are no records in the child tables...I would still require records to be printed in the XML. For example..tab3 does not have matching record... but still I need null tags to be part of the XML. Can you please help me here.
SELECT XMLELEMENT (
"PH1",
XMLELEMENT ("PH1_SEQ_NUM", 'PH1_' || ph1.ph1_seq_num),
XMLELEMENT (
"PH1_RECORD_ID",
XMLATTRIBUTES ('N' AS "Error_Flag", 'N' AS "Error_Msg"),
ph1.ph1_record_id),
(SELECT XMLAGG (
XMLELEMENT (
"PH2",
XMLELEMENT ("PH2_SEQ_NUM",
'PH2_' || ph2.ph2_seq_num),
XMLELEMENT (
"PH2_RECORD_ID",
XMLATTRIBUTES ('N' AS "Error_Flag",
'N' AS "Error_Msg"),
ph2.ph2_record_id))
ORDER BY ph2.ph2_seq_num)
FROM tab2 ph2
WHERE ph1.ph1_seq_num = ph2.ph2_ph1_seq_num),
(SELECT XMLAGG (
XMLELEMENT (
"PH3",
XMLELEMENT ("PH3_SEQ_NUM",
'PH3_' || ph3.ph3_seq_num),
XMLELEMENT (
"PH3_RECORD_ID",
XMLATTRIBUTES ('N' AS "Error_Flag",
'N' AS "Error_Msg"),
ph3.ph3_record_id))
ORDER BY ph3.ph3_seq_num)
FROM tab3 ph3
WHERE ph1.ph1_seq_num = ph3.ph3_ph1_seq_num))
AS xml_data
FROM tab1 ph1
WHERE ph1_seq_num = 626713;
Database Version
Oracle Database 11g Release 11.2.0.4.0 - 64bit
PL/SQL Release 11.2.0.4.0