Monday, February 28, 2011

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

No comments:

Post a Comment