2 Replies Latest reply on Sep 27, 2019 5:16 PM by user6314670

    Root element should return NULL if no data in source

    user6314670

      Hi All, Wrote the below query and per requirement, it is returning the result as expected but if there is no data into the table then it should not return anything, neither root element (second query output), should return NULL. Could you please help me correcting below query or the solution to resolve this issue. Thanks in advance for your help. with xml_data as (select XMLELEMENT("DailyTNA",                     XMLATTRIBUTES('USD' AS "Currency", '2014-12-18' as "Date"),                     to_char(3510000.0,                             'FM999999999999999999999999999990D0999999999')) as xmldoc,         1 as sort_ord,         '2014-12-18' as data_date     from dual   union all   select XMLELEMENT("SharesOutstanding",                     XMLATTRIBUTES('2013-12-31' as "Date"),                     to_char(17178383.0,                             'FM999999999999999999999999999990D0999999999')) as xmldoc,         2 sort_ord,         '2013-12-31' as data_date     from dual) select XMLELEMENT("Profile",                   XMLELEMENT("AssetValues",                             XMLAgg(xmldoc order by sort_ord, data_date desc)))   from xml_data Output 3510000.017178383.0 with xml_data as (select NULL as xmldoc, 1 as sort_ord, '2014-12-18' as data_date     from dual   union all   select NULL as xmldoc, 2 sort_ord, '2013-12-31' as data_date     from dual) select XMLELEMENT("Profile",                   XMLELEMENT("AssetValues",                             XMLAgg(xmldoc order by sort_ord, data_date desc)))   from xml_data Output Thanks