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.

Consider a sample table:

  CREATE TABLE cities (name VARCHAR(30), country VARCHAR(30), flag CHAR(1));
 
  -- Insert sample rows
  INSERT INTO cities VALUES ('Seville', 'Spain', 'S');
  INSERT INTO cities VALUES ('Warsaw', 'Poland', 'P');
  INSERT INTO cities VALUES ('Boston', 'United States', 'U');

Oracle:

  SELECT XMLELEMENT(NAME "country", country)
  FROM cities;

SQL Server:

  SELECT (SELECT [data()] = country FOR XML PATH('country'), TYPE)
  FROM cities;

Both queries return 3 rows:

  <country>Spain</country>
  <country>Poland</country>
  <country>United States</country>

You can also add an attribute to XML elements as follows:

Oracle:

  SELECT XMLELEMENT(NAME "country", XMLATTRIBUTES(flag AS "flag"), country)
  FROM cities;

SQL Server:

  SELECT (SELECT flag AS [@flag], [data()] = country FOR XML PATH('country'), TYPE)
  FROM cities;

Both queries return 3 rows:

  <country flag="S">Spain</country>
  <country flag="P">Poland</country>
  <country flag="U">United States</country>

You can also created nested XML elements with attributes as follows:

Oracle:

  SELECT XMLELEMENT(NAME "country", XMLATTRIBUTES(country AS "attr"), XMLELEMENT(NAME "cities", name))
  FROM cities;

SQL Server:

  SELECT 
    (SELECT country AS [@attr], (SELECT [data()] = name FOR XML PATH('cities'), TYPE) 
       FOR XML PATH('country'), TYPE)
  FROM cities

Both queries return 3 rows:

  <country attr="Spain"><cities>Seville</cities></country>
  <country attr="Poland"><cities>Warsaw</cities></country>
  <country attr="United States"><cities>Boston</cities></country>

For more information, see Oracle to SQL Server Migration.