Search code examples
sql-serverxmldb2sql-server-2019for-xml

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


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?


Solution

  • 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