I have the following code in a select statement today for a DB2 Database which does work. I would like to convert this code for SQL Server 2019. So far, I haven't found anything that would replicate the same output.
SELECT CASE
WHEN APPLICATION = 'APP1' THEN 'APPLICATION1'
WHEN APPLICATION = 'APP2' THEN 'APPLICATION2'
ELSE NULL
END AS "ACCESS",
XMLSERIALIZE (
CONTENT XMLELEMENT(
NAME "Users",
XMLAGG(
XMLELEMENT(
NAME "User",
XMLELEMENT (NAME "NAME", USERID),
XMLELEMENT (NAME "FIRST", RTRIM(FIRST)),
XMLELEMENT (NAME "LAST", RTRIM(LAST)),
XMLELEMENT (NAME "FROM", RTRIM(SUBMITTEDBY_ID)),
XMLELEMENT (NAME "DISPLAYNAME", RTRIM(DISPLAYNAME))
)
ORDER BY APPLICATION
)
)
AS CLOB (30K)
) AS "USERLIST"
GROUP BY APPLICATION
From RequestTable
The output has one column called ACCESS and the second column would be called USERLIST and that column would be an XML formatted output.
ACCESS | USERLIST |
---|---|
APPLICATION1 | <Users><User><NAME>user1</NAME><FIRST>givenName1</FIRST><LAST>surName1</LAST><FROM>manager</FROM><DISPLAYNAME>givenName1 surName1</DISPLAY></User><User><NAME>user2</NAME><FIRST>givenName2</FIRST><LAST>surName2</LAST><FROM>manager</FROM><DISPLAYNAME>givenName2 surName2</DISPLAY></User></Users> |
APPLICATION2 | <Users><User><NAME>user3</NAME><FIRST>givenName3</FIRST><LAST>surName3</LAST><FROM>manager</FROM><DISPLAYNAME>givenName3 surName3</DISPLAY></User></Users> |
Does SQL Server have similar functionality I can take advantage of?
You can do this in a single scan of the base table, by using STRING_AGG
. Unfortunately, SQL Server does not support XML_AGG
, which would have made this significantly easier.
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;