XMLELEMENT Function - IBM DB2 to SQL Server Migration

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

DB2:

  SELECT XMLELEMENT(NAME "country", val)
  FROM
  ( 
    SELECT 'Spain' AS val FROM sysibm.sysdummy1
    UNION ALL
    SELECT 'Turkey' AS val FROM sysibm.sysdummy1
    UNION ALL
    SELECT 'Greece' AS val FROM sysibm.sysdummy1
  ) 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 IBM DB2 to SQL Server Migration.