select
xmlelement(noentityescaping "msg",
xmlattributes( xalt.originating_timestamp as "time",
xalt.organization_id as "org_id",
xalt.component_id as "comp_id",
xalt.message_id as "msg_id",
xalt.message_type as "type",
xalt.message_group as "group",
xalt.message_level as "level",
xalt.host_id as "host_id",
xalt.host_address as "host_addr",
xalt.process_id as "pid_id",
xalt.version as "version"
),
xmlelement("txt", regexp_replace (message_text, '\s+', ' ') )
) as "mylog.xml"
from
x$dbgalertext xalt
where
originating_timestamp > sysdate-1
and
(
message_text like '%ORA%'
or
message_text like '%Fatal%'
);
Output: