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.