Hi All,
I am trying to use xmlagg on a query to generate xml file. In the example 18-12 of this link https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB5020 can I not have the Department tag? I tried removing the xmlelement but when I am trying to insert the result set into a clob column I am getting "ORA-19010: Cannot insert XML fragments"
SELECT XMLAgg(XMLElement("Employee",
e.job_id||' '||e.last_name)
ORDER BY e.last_name)
AS "Dept_list"
FROM hr.employees e
I am looking for a output something like this
Dept_list
------------------
<Employee>PU_CLERK Baida</Employee>
<Employee>PU_CLERK Colmenares</Employee>
<Employee>PU_CLERK Himuro</Employee>
<Employee>PU_CLERK Khoo</Employee>
<Employee>HR_REP Mavris</Employee>
<Employee>PU_MAN Raphaely</Employee>
<Employee>PU_CLERK Tobias</Employee>
1 row selected.
I want all the rows in 1 column so I can insert the result set into clob column.
We are on DB version 12.1.0.2.0
Appreciate any suggestions on how to remove the root tag and maintain proper xml structure so I can insert it into clob column.
Thanks in advance!