How to use SQL XML and XMLAGG to return multiple xmlelements

In SQL, I need to create xml code that looks like this:

    <Phone>
       <PhoneTypeCode tc="12">Mobile</PhoneTypeCode>
       <Area>801</Area>
       <DialNumber>9996666</DialNumber>
    </Phone>
    <Phone>
       <PhoneTypeCode tc="2">Business</PhoneTypeCode>
       <Area>801</Area>
       <DialNumber>1113333</DialNumber>
    </Phone>

using xmlagg, but it is throwing an error on the ',' after p.desc

How does this IBM DB2 SQL function need to be fixed to achieve the above xml?

    select  
      xmlelement(Name "Phone", 
        xmlagg(xmlelement(name "PhoneTypeCode", 
               xmlattributes(trim(p.phtype) as "tc"), trim(p.desc)),
           xmlelement(name "AreaCode", p.area),
           xmlelement(name "DialNumber", p.phone)
            )                   
      ) as xml
    from phone p
    where p.entityid = #entity_id

I also wanted to add that it does compile and run with this:

    select  
      xmlelement(Name "Phone", 
        xmlagg(xmlelement(name "PhoneTypeCode", 
               xmlattributes(trim(p.phtype) as "tc"), trim(p.desc))
        )                   
      ) as xml
    from phone p
    where p.entityid = #entity_id

Here is what it returns:

    <Phone>
       <PhoneTypeCode tc="12">Mobile</PhoneTypeCode>
       <PhoneTypeCode tc="2">Business</PhoneTypeCode>
    </Phone>

But of course, I need the Area and DialNumber. It is as if you can't have more than one xmlelement within an xmlagg.

Solution

How does this IBM DB2 SQL function need to be fixed to achieve the above xml?

Firstly, you may want to count your parentheses. Normally one would want as many closing parentheses as there are opening parentheses.

Secondly, you don't need XMLAGG() at all. You'd use it when inserting multiple elements of the same type, based on multiple relational records, into a single outer element, like

 <phones>
   <phone no="1" .../>
   <phone no="2" .../>
   ...
 </phones>

For you something like this should work:

  with phone (phtype, desc, area, phone) as 
     (values ('home','blah','555','555-5555'),('office','blah','555','555-1111'))
  select  
    xmlelement(
      Name "Phone", 
      xmlelement(
        name "PhoneTypeCode", 
        xmlattributes(
           trim(p.phtype) as "tc"
        ), 
        trim(p.desc)
      ),
      xmlelement(name "AreaCode", p.area),
      xmlelement(name "DialNumber", p.phone)                   
    ) as xml
  from phone p