By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
APPLICATION CHAR(4),
USERID VARCHAR(20),
FIRST VARCHAR(20),
LAST VARCHAR(20),
SUBMITTEDBY_ID VARCHAR(20),
DISPLAYNAME VARCHAR(20)
);
INSERT YourTable (APPLICATION, USERID, FIRST, LAST, SUBMITTEDBY_ID, DISPLAYNAME) VALUES
('APP1', 'user1', 'givenName1', 'surName1', 'manager', 'givenName1'),
('APP1', 'user2', 'givenName2', 'surName2', 'manager', 'givenName2'),
('APP2', 'user3', 'givenName3', 'surName3', 'manager', 'givenName3');
-- DDL and sample data population, end
3 rows affected
SELECT
ACCESS = IIF(APPLICATION = 'APP1', 'APPLICATION1', 'APPLICATION2'),
USERLIST = CAST('<Users>' + STRING_AGG(x.xml, '') WITHIN GROUP (ORDER BY USERID) + '</Users>' AS xml)
FROM YourTable t
CROSS APPLY (
SELECT
t.USERID,
t.FIRST,
t.LAST,
t.SUBMITTEDBY_ID AS [FROM],
t.DISPLAYNAME
FOR XML PATH('User')
) x(xml)
GROUP BY APPLICATION;
| ACCESS | USERLIST |
|---|---|
| APPLICATION1 | <Users><User><USERID>user1</USERID><FIRST>givenName1</FIRST><LAST>surName1</LAST><FROM>manager</FROM><DISPLAYNAME>givenName1</DISPLAYNAME></User><User><USERID>user2</USERID><FIRST>givenName2</FIRST><LAST>surName2</LAST><FROM>manager</FROM><DISPLAYNAME>givenName2</DISPLAYNAME></User></Users> |
| APPLICATION2 | <Users><User><USERID>user3</USERID><FIRST>givenName3</FIRST><LAST>surName3</LAST><FROM>manager</FROM><DISPLAYNAME>givenName3</DISPLAYNAME></User></Users> |