Powered by Syntax Highlighter scripts!

FOR XML PATH in Microsoft SQL Server for quick debugging on Multiple Table Joins

I was having a case where I need to search users who have more than 1 role and also see the Role names at the same time. It is not for use in application and its for me to debug on an issue.
I found that the FOR XML PATH clause on SELECT statement can be used to extract all of this information in one shot.

SELECT ID, FIRST_NAME, LAST_NAME, USERNAME,
 (SELECT COUNT(1) FROM USER_ROLE UR WHERE UR.USER_ID = AU.ID) AS ROLE_COUNT,
 (SELECT R.NAME as role FROM ROLE R, USER_ROLE UR
  WHERE R.ID = UR.ROLE_ID
  AND UR.USER_ID = TU.ID
  FOR XML PATH('')
 ) AS ROLE_NAMES
FROM MY_APP_USER AU
WHERE (SELECT COUNT(1) FROM USER_ROLE UR WHERE UR.USER_ID = AU.ID) > 1
;

No comments: