Example of XMLAgg, XMLElement and XMLAttributes

This query can provide list of Oracle Applications users and responsibilities assigned to them in xml format.

SELECT XMLElement
       ( "UsersList"
       , XMLAgg 
         ( XMLElement
           ( "Users"
           , XMLAttributes
             ( usr.user_id                             as "Id"
             , user_name                               as "Name"
             , nvl(usr.description,'Null')             as "Description"
             , to_char(creation_date   ,'DD-MON-YYYY') as "CreatedOn"
             , to_char(last_update_date,'DD-MON-YYYY') as "LastUpdatedOn"
             , usr.end_date                            as "EndDate"
             )
           , (SELECT XMLAgg
                     ( XMLElement
                       ( "Responsibility"
                       , XMLAttributes
                         ( urgd.responsibility_id                 as "Id"
                         , urgd.responsibility_application_id     as "Appl_id"
                         , resp.responsibility_name               as "Name"
                         , to_char(urgd.start_date,'dd-mon-yyyy') as "StartDate"
                         , DECODE(urgd.end_date,NULL,'NULL',
                                               TO_CHAR(urgd.end_date,'dd-mon-yyyy')
                                 )                                as "EndDate"
                         )
                       )
                     )
                FROM apps.FND_USER_RESP_GROUPS_direct urgd
                   , apps.fnd_responsibility_vl resp
               WHERE urgd.user_id                       = usr.user_id
                 AND urgd.responsibility_id             = resp.responsibility_id
                 AND urgd.responsibility_application_id = resp.application_id
             )
           )
         )
       ).getclobval()
  FROM apps.fnd_user  usr
 WHERE ROWNUM < 20