I am trying to run a simple XML extraction on a large DB just want to create a table with one column and a lot of data (to view in a browser).
I use SPUFI to run my query but I assume it puts all the data in one record so I am limited by the record size and cant get to the bottom of the results this way.
Is there any other way to do this? or should I just increase record size?
SELECT
XML2CLOB(
XMLELEMENT (NAME "TABLE",
XMLATTRIBUTES ('1' AS "BORDER",'width: 70%; height: 70px' AS "style",'center' AS "align" ),
XMLELEMENT(NAME CAPTION,'DEPARTMENT-EMPLOYEE TABLE'),
XMLELEMENT(NAME tr,
XMLFOREST( 'DEPARTMENT' AS TH
)
),
XMLAGG(
XMLELEMENT(NAME tr,
XMLFOREST( E.ACCOUNT_NUMBER AS td
)
)
)
)
)
FROM UTILDB.DAILY_GLDM_BALANCE E where BRANCH='1144'
;

I'm not sure I made not misunderstand your requirement.

I thought your required output is like:
<TABLE BORDER=1 style="width: 70&#37;; height: 70px" align=center>
<CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION>
<TR><TH>DEPARTMENT</TH></TR>
<TR><TD>account_number 1</TD></TR>
<TR><TD>account_number 2</TD></TR>
.....
<TR><TD>account_number n</TD></TR>
</TABLE>

So, if numbering to each lines as following:
<!-- 01: 00001 --> <TABLE BORDER=1 style="width: 70%; height: 70px" alighn=center>
<!-- 01: 00002 --> <CAPTION>DEPARTMENT-EMPLOYEE TABLE</CAPTION>
<!-- 01: 00003 --> <TR><TH>DEPARTMENT</TH></TR>
<!-- 10: 00001 --> <TR><TD>account_number 1</TD></TR>
<!-- 10: 00002 --> <TR><TD>account_number 2</TD></TR>
.....
<!-- 10: nnnnn --> <TR><TD>account_number n</TD></TR>
<!-- 90: 00001 --> </TABLE>

query might be something like:
Or
SELECT data
FROM (my previous full-select using UNION ALL) q
ORDER BY sequence

Thanks a lot. I am trying to improve in this and your examples are really helpful.