XML output using XMLAgg() problem 1

[olmos]

I have tried the query using XMLAgg() , but my output does not contain the closing tags for Department </DEPARTMENT> it seems to be cut off for some reason. <Employee>PRESID got cut off also, it should be PRESIDENT and MANAG should have been MANAGER.

I am using Oracle 9i r2, windows version on sqlplus worksheet.

Why is it skipping the closing tag for Department and How can I fix this?

Thanks,
olmos
--------------
Query :
---------------
SELECT XMLELEMENT(&quot;Department&quot;, XMLAttributes(deptno AS deptno),
XMLAGG(XMLELEMENT(&quot;Employee&quot;, e.job||' '||e.ename)))
AS &quot;Dept_list&quot;
FROM emp e
GROUP BY e.deptno;
--------------------------
The output I get is :
--------------------------
Dept_list

<Department DEPTNO=&quot;10&quot;>
<Employee>MANAGER CLARK</Employee>
<Employee>PRESID

<Department DEPTNO=&quot;20&quot;>
<Employee>CLERK SMITH</Employee>
<Employee>ANALYST

<Department DEPTNO=&quot;30&quot;>
<Employee>SALESMAN ALLEN</Employee>
<Employee>MANAG

 

[sem]

I suppose that this is due to long size currently being used. Try to increase it:

set long <greater size>

Regards, Dima

 

[olmos]

Thank you Dima!

It was set at 80 and now I have increased it to 1000.

Oracle info-
SQL*Plus by default only shows the first 80 bytes of any LONG, CLOB and NCLOB datatypes. The data is there, but since sqlplus is a command-line tool it tries not to
print out too much data. You can override this to tell sqlplus exactly how many bytes you want to see:

 

[olmos]

I have noticed that I have another problem, this time with using SYS_XMLGEN
query. It is supposed to always return a well-formed XML document.

Query
-------------
SELECT SYS_XMLGEN(empno)
FROM emp WHERE ename LIKE 'SMITH%';

My Result is :
--------------
SYS_XMLGEN(EMPNO)

<EMPNO>7369</EMPNO>


It should have the xml version on the first line. like this-
<?xml version=''1.0''?>
<empno>60</empno>

Is there some setting I must change, what am I doing wrong?

thanks,
Olmos