XMLFOREST Function - Oracle to SQL Server Migration

In Oracle 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.

Oracle:

  SELECT XMLFOREST(country, city)
  FROM
  ( 
    SELECT 'Spain' AS country, 'Malaga' AS city FROM dual
    UNION ALL
    SELECT 'Turkey' AS country, 'Alanya' AS city FROM dual
    UNION ALL
    SELECT 'Greece' AS country, 'Marmaras' AS city FROM dual
  ) 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 Oracle to SQL Server Migration.