Convert DB2 Query to SQL Server to output a grouped column in XML Format

Question

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?

Answer 1

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;

db<>fiddle

Answer 2

You can do something like this:

-- Create test data
-- drop table #data
select access
,   n.value('NAME[1]', 'NVARCHAR(100)') AS USERID
,   n.value('FIRST[1]', 'NVARCHAR(100)') AS FIRST
,   n.value('LAST[1]', 'NVARCHAR(100)') AS LAST
,   n.value('FROM[1]', 'NVARCHAR(100)') AS [SUBMITTEDBY_ID]
,   n.value('DISPLAYNAME[1]', 'NVARCHAR(100)') AS [DISPLAYNAME]
into    #data
from (
    VALUES  (N'APPLICATION1', N'<Users><User><NAME>user1</NAME><FIRST>givenName1</FIRST><LAST>surName1</LAST><FROM>manager</FROM><DISPLAYNAME>givenName1 surName1</DISPLAYNAME></User><User><NAME>user2</NAME><FIRST>givenName2</FIRST><LAST>surName2</LAST><FROM>manager</FROM><DISPLAYNAME>givenName2 surName2</DISPLAYNAME></User></Users>')
    ,   (N'APPLICATION2', N'<Users><User><NAME>user3</NAME><FIRST>givenName3</FIRST><LAST>surName3</LAST><FROM>manager</FROM><DISPLAYNAME>givenName3 surName3</DISPLAYNAME></User></Users>')
) t (ACCESS,USERLIST)
cross apply (
    select cast(userlist as xml) AS x
    ) x
cross apply x.x.nodes('Users/User') n(n)

-- Generate query
select  ACCESS, (SELECT USERID, FIRST, LAST, SUBMITTEDBY_ID AS [FROM], DISPLAYNAME FROM #data d where d.access = x.access ORDER BY USERID for xml path('User'), ROOT('Users')) AS USERLIST
from    (
    SELECT access
    FROM    #data
    group by access
    ) x

There's no group by XML thingy in SQL Server, but you can group by the source data, and then use a correlated subquery to join together table another time for the XML source.

I added a dummy order by on userId, you can probably fix it to order by something else.

Answer 3

Please try the following solution.

It is following the same minimal reproducible example pattern. You copy it to SSMS as-is, run it, and it is working.

It is better to use native XML datatype in SQL Server. It occupies much less storage because it is compressed. All this in comparison with CLOB, a.k.a. VARCHAR(MAX) in MS SQL Server parlance. Also, XML data type has a powerful XQuery/XSD based API to deal with.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    APPLICATION CHAR(4), 
    USERID VARCHAR(20),
    FIRST VARCHAR(20),
    LAST VARCHAR(20),
    SUBMITTEDBY_ID VARCHAR(20),
    DISPLAYNAME VARCHAR(20)
);
INSERT @tbl (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

SELECT ACCESS = IIF(APPLICATION = 'APP1', 'APPLICATION1', 'APPLICATION2'), 
    (SELECT USERID, FIRST, LAST, SUBMITTEDBY_ID AS [FROM], DISPLAYNAME 
    FROM @tbl AS c -- c(hild)
    WHERE c.APPLICATION = p.APPLICATION 
    ORDER BY USERID 
    FOR xml path('User'), TYPE, ROOT('Users')) AS USERLIST
FROM @tbl AS p -- p(arent)
GROUP BY APPLICATION;