Thursday 17 July 2014

How to creating XML tag in SQL Query

/* Formatted on 7/17/2014 12:44:27 PM (QP5 v5.115.810.9015) */
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

No comments:

Post a Comment