I am trying to used a stored proc to return an xml string from sysibm.sqltables formatted as follows:

<TABLES>
<schema name='schema1'>
<table>table1</table>
<table>table2</table>
</schema>
<schema name='schema2'>
<table>table3</table>
<table>table4></table>
</schema>
</TABLES>

The closest I can get is as follows:

SELECT XML2CLOB (XMLELEMENT (NAME "TABLES", XMLELEMENT (NAME "schema", XMLATTRIBUTES (table_schem AS "name"),
XMLAGG (XMLFOREST(table_name AS "table")))))
FROM sysibm.sqltables GROUP BY table_schem;

This produces a separate result for each schema because of the GROUP BY statement, but if this is left out an error occurs saying that table_schem needs to be in a GROUP BY statement.

Does anyone have any ideas?

Many thanks,
Ian

Why would you want to use GROUP BY, you don't use any aggerate function. I think ORDER BY is appropriate.

I am using the group by statement, because using the alternative suggested give the error message below:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0119N An expression starting with "TABLE_SCHEM" specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
column function and no GROUP BY clause is specified. LINE NUMBER=5.
SQLSTATE=42803

Thanks,
Ian