net.javaforge.blog - Oracle: Aggregating SQL statement results as single XML CLOB

Once again a short reminder how to aggregate SQL statement execution results as a single XML CLOB in Oracle.

Assume there is an Oracle table containing user data:

create table t_user(
	user_id		number(15) not null,	
	user_name	varchar2(100) not null,		
	role		varchar2(100) not null,			
	email		varchar2(100)	
)
/

Now aggregating all the user entries as single XML CLOB can be done using following statement:

select xmlelement("users",
 xmlagg(xmlelement("user",
 xmlattributes( t.user_id as "id",
 t.user_name as "name",
 t.role,
 t.email
 )
 ))
 ).getclobval() xml
from t_user t

This will result in a CLOB that looks like this:

<users>
	<user id="1" name="foo" role="USER" email="foo@domain.com"></user>
	<user id="2" name="bar" role="ADMIN" email="bar@domain.com"></user>
	...
	<user id="999" name="xyz" role="USER" email="xyz@domain.com"></user>
</users>