SQL/XML nested child problem

[engelenfrederik]

Hello all,

I ran into an interesting SQL/XML question yesterday. A customer wants
to make a part of the relational data available through a webservice
as XML. This seemed like the perfect opportunity to use the DB2 XML-
capabilities. The following test scenario will make things easier to
explain:

drop table test.customer;
create table test.customer
(
id integer not null,
name varchar(255) not null,
email varchar(255),
customer_type char(1),
contact_of_customer_id integer, -- points to a custom from whom this
custom is a contact
primary key(id)
);

insert into test.customer(id, name, email, customer_type,
contact_of_customer_id) values (1, 'name1', '[e-mail]', 'A', null), (2,
'name2', '[e-mail]', 'A', null), (3, 'name3', '[e-mail]', 'B', 2), (4,
'name4', '[e-mail]', 'C', 2);

The database structure allows for customers to have contacts (which in
turn are customers as well, etc). In practice, the application will
allow only one level of contacts, so no recursion is required. As you
can see, customer 1 and 2 are of the required type 'A', 1 having no
contacts, 2 having customers 3 and 4 as contact.

The XML should contain a list of customers (including their details),
and each one that has contacts should have them listed as child nodes.
This gives the following (the output format is fixed, I can't change
it):

<customers>
<customer>
<customerId>1</customerId>
<customerInfo>
<customerName>name1</customerName>
<customerEmail>[e-mail]</customerEmail>
</customerInfo>
</customer>
<customer>
<customerId>2</customerId>
<customerInfo>
<customerName>name2</customerName>
<customerEmail>[e-mail]</customerEmail>
</customerInfo>
<customerContact>
<customerId>3</customerId>
<customerInfo>
<customerName>name3</customerName>
<customerEmail>[e-mail]</customerEmail>
</customerInfo>
</customerContact>
<customerContact>
<customerId>4</customerId>
<customerInfo>
<customerName>name4</customerName>
<customerEmail>[e-mail]</customerEmail>
</customerInfo>
</customerContact>
</customer>
</customers>

I've created the following xquery that returns one record containing
the neccesary XML:

XQUERY
let $customers := db2-fn:sqlquery(
"
select
XMLELEMENT
(
NAME ""customer"",
XMLELEMENT
(
NAME ""customerId"",
cust.id
),
XMLELEMENT
(
NAME ""customerInfo"",
XMLFOREST
(
cust.name as ""customerName"",
cust.email as ""customerEmail""
)
),
XMLAGG
(
XMLELEMENT
(

NAME ""customerContact"",
XMLELEMENT
(
NAME ""customerId"",
cont.id
OPTION NULL ON NULL
),
XMLELEMENT
(
NAME ""customerInfo"",
XMLFOREST
(
cont.name as ""customerName"",
cont.email as ""customerEmail""
)
OPTION NULL ON NULL
)
OPTION NULL ON NULL
)
)
)

from test.customer cust
left outer join test.customer cont on cont.contact_of_customer_id =
cust.id
where cust.customer_type = 'A'
group by cust.id, cust.name, cust.email
")

return
<customers>{$customers}</customers>;

It selfjoins customer (cust and cont) and groups by the fields in
cust. The XMLAGG function does the rest. This was based on the example
used in the SQL Reference Vol.1: XMLELEMENT, regarding the
departments.

The query functions correctly. Notice however the GROUP BY-statement
at the end of it. The real table contains much more data, including
data from other tables as well. With this query, I have to GROUP BY on
every column I want to include in customer. This seems unnecessary and
wasteful. I am sure that there is a better way to write this one.

XQuery guru's (and even lesser gods), I would like to ask you to take
your best shot at it.

Many thanks in advance,

Frederik