XMLFOREST Function - IBM DB2 to SQL Server Migration

In DB2 XMLFOREST function allows you to create multiple XML elements (values with opening and closing tags). In SQL Server you can the special form of SELECT … FOR XML PATH expression.

DB2:

  SELECT XMLFOREST(country, city)
  FROM
  ( 
    SELECT 'Spain' AS country, 'Malaga' AS city FROM sysibm.sysdummy1
    UNION ALL
    SELECT 'Turkey' AS country, 'Alanya' AS city FROM sysibm.sysdummy1
    UNION ALL
    SELECT 'Greece' AS country, 'Marmaras' AS city FROM sysibm.sysdummy1
  ) t;

SQL Server:

  SELECT
     CAST(CONCAT(
       (SELECT [data()] = country FOR XML PATH('country')),
       (SELECT [data()] = city FOR XML PATH('city'))
     ) AS XML) 	
  FROM
  ( 
    SELECT 'Spain' AS country, 'Malaga' AS city
    UNION ALL
    SELECT 'Turkey' AS country, 'Alanya' AS city
    UNION ALL
    SELECT 'Greece' AS country, 'Marmaras' AS city
  ) t;

Both queries return 3 rows:

  <country>Spain</country><city>Malaga</city>
  <country>Turkey</country><city>Alanya</city>
  <country>Greece</country><city>Marmaras</city>

For more information, see IBM DB2 to SQL Server Migration.