XMLELEMENT Function - Oracle to SQL Server Migration

In Oracle XMLELEMENT function allows you to create a XML element (an value with opening and closing tags). In SQL Server you can the special form of SELECT … FOR XML PATH expression.

Oracle:

  SELECT XMLELEMENT("country", val)
  FROM
  ( 
    SELECT 'Spain' AS val FROM dual
    UNION ALL
    SELECT 'Turkey' AS val FROM dual
    UNION ALL
    SELECT 'Greece' AS val FROM dual
  ) t;

SQL Server:

  SELECT (SELECT [data()] = val FOR XML PATH('country'), TYPE)
  FROM
  ( 
    SELECT 'Spain' AS val
    UNION ALL
    SELECT 'Turkey' AS val
    UNION ALL
    SELECT 'Spain' AS val
  ) t;

Both queries return 3 rows:

  <country>Spain</country>
  <country>Turkey</country>
  <country>Greece</country>

For more information, see Oracle to SQL Server Migration.