How to use SQL XML and XMLAGG to return multiple xmlelements

Question

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.

mustaccio · Accepted Answer · 2013-05-23T19:42:55.360

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

Thanks for taking a look. I fixed the number of parenthesis. I also added the as "desc" and got this error: [SQL0199] Keyword AS not expected. Valid tokens: ) and it highlighted the one just added. — user2414410, May 23 '13 at 19:06
I misunderstood your original requirement. Please see the corrected answer. — mustaccio, May 23 '13 at 19:43

How to use SQL XML and XMLAGG to return multiple xmlelements

1 Answers1