SA-17: Need help for final step to create proper XML string

[fvestjens]

I'm using SQL anywhere 17.0.11 build 6933.

I'm using the SQL Anywhere 17 demo database to explain what I'm trying to achieve.
I created the following query to generate an XML line per customer:

select 
 xmlconcat(xmlelement(name Customer,
                         (select xmlconcat(XMLelement(name Name,CompanyName)
                                          ,XMLelement(name City,City)
                                          ,(select 
                                              SurName ||', '|| GivenName as FullName
                                            from groupo.Contacts
                                            where CustomerID = groupo.Customers.Id 
                                            for xml auto, elements)
                                          )
                         ) 
                     )
          )
from groupo.Customers where Id between 117 and 119

For very customer I get a line returned in the format I'm lookgin for:

<textarea rows="3" cols="180">
<customer><name>Square Sports</name><city>Unionville</city></customer>
<customer><name>Raleigh Active Wear</name><city>Chandler</city></customer>
<customer><name>Ocean Sports</name><city>Owen Sound</city><contacts><fullname>Hildebrand, Jane</fullname></contacts></customer>
</textarea>

So far so good. But now I want these lines added into 1 XML string surrounded by Customers.
So it should look like:

<customers>
   <customer>
      <name>Square Sports</name>
      <city>Unionville</city>
   </customer>
   <customer>
      <name>Raleigh Active Wear</name>
      <city>Chandler</city>
   </customer>
   <customer>
      <name>Ocean Sports</name>
      <city>Owen Sound</city>
      <contacts><fullname>Hildebrand, Jane</fullname></contacts>
   </customer>
</customers>

Anyone any idea how to achieve this?

[VolkerBarth]

I don't know whether this is a good solution, the XML functions are still something I usually have to fiddle around with...- but XMLAGG is used to generate the sequence of customer nodes, and an outer XMLELEMENT is required for the root node:

select xmlelement(name Customers, 
   xmlagg(xmlconcat(xmlelement(name Customer,
      (select xmlconcat(XMLelement(name Name,CompanyName)
                                            ,XMLelement(name City,City)
                                            ,(select 
                                                 SurName ||', '|| GivenName as FullName
                                               from groupo.Contacts
                                               where CustomerID = groupo.Customers.Id 
                                               for xml auto, elements)
))))))
from groupo.Customers where Id between 117 and 119

[fvestjens]

Thanks a lot. This seems to be the solution.
I've been fiddling around for a day to get what I already had but couldn't figure out the last step.

I'll adjust my queries in production and see what it will do.

Thanks a lot.