SELECT FOR XML PATH - SQL Server to PostgreSQL Migration

In SQL Server, SELECT FOR XML PATH statement allows you to convert a multi-row multi-column query result to a single-row single-column XML value.

Consider a sampe table:

  CREATE TABLE colors (name VARCHAR(30), category CHAR(1));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green', 'G');
  INSERT INTO colors VALUES ('White', 'W');
  INSERT INTO colors VALUES ('Black', 'B');

In SQL Server, the FOR XML PATH clause of SELECT statement to aggregate values from multiple rows and produces single-row XML:

SQL Server:

  -- Create single-row XML
  SELECT name, category AS cat FROM colors FOR XML PATH;
  # <row><name>Green</name><cat>G</cat></row><row><name>White</name>
  # <cat>W</cat></row><row><name>Black</name><cat>B</cat></row>

By default, <row></row> tag were added around each row value, you can remove them using PATH(''):

  -- Empty PATH('') removes <row></rows> tags
  SELECT name, category AS cat FROM colors FOR XML PATH('');
  # <name>Green</name><cat>G</cat><name>White</name><cat>W</cat><name>Black</name>
  # <cat>B</cat>

Or you can redefine row tags:

  -- Use <item></item> as row tags
  SELECT name, category AS cat FROM colors FOR XML PATH('item');
  # <item><name>Green</name><cat>G</cat></item><item><name>White</name><cat>W</cat>
  # </item><item><name>Black</name><cat>B</cat></item>

In PostgreSQL you can use XMLAGG, XMLELEMENT and XMLFOREST functions as follows:

PostgreSQL:

  -- Create single-row XML with <row></rows> tags
  SELECT XMLAGG(XMLELEMENT(NAME row, XMLFOREST(name, category AS cat))) FROM colors;
  # <row><name>Green</name><cat>G</cat></row><row><name>White</name>
  # <cat>W</cat></row><row><name>Black</name><cat>B</cat></row>

Row tag is not generated if you skip XMLELEMENT:

  -- Without <row></rows> tags
  SELECT XMLAGG(XMLFOREST(name, category AS cat)) FROM colors;
  # <name>Green</name><cat>G</cat><name>White</name><cat>W</cat><name>Black</name>
  # <cat>B</cat>

Or you can define any other name for row tag:

  -- Or redefining row tags with <item></item>
  SELECT XMLAGG(XMLELEMENT(NAME item, XMLFOREST(name, category AS cat))) FROM colors;
  # <item><name>Green</name><cat>G</cat></item><item><name>White</name><cat>W</cat>
  # </item><item><name>Black</name><cat>B</cat></item>

By default, SQL Server returns XML data as NVARCHAR data type, but you can specify TYPE clause to return XML data type:

SQL Server:

  -- Return XML data as XML data type
  SELECT category AS cat FROM colors FOR XML PATH('item'), TYPE;
  # <item><cat>G</cat></item><item><cat>W</cat></item><item><cat>B</cat></item>

In PostgreSQL, the XMLAGG function returns XML data type.

By default, SQL Server returns XML data without the root node, but you can specify the ROOT clause to define the root node:

SQL Server:

  -- Return XML data with <root></root> root node
  SELECT category AS cat FROM colors FOR XML PATH('item'), ROOT;
  # <root><item><cat>G</cat></item><item><cat>W</cat></item><item><cat>B</cat></item></root>
 
  -- Use <items></items> as the root node
  SELECT category AS cat FROM colors FOR XML PATH('item'), ROOT('items');
  # <items><item><cat>G</cat></item><item><cat>W</cat></item><item><cat>B</cat></item></items>

In PostgreSQL, you can add XMLELEMENT for the root node as follows:

PostgreSQL:

   -- Use <items></items> as the root node
   SELECT XMLELEMENT(NAME items, XMLAGG(XMLELEMENT(NAME item, XMLFOREST(category AS cat))))
   FROM colors;
   # <items><item><cat>G</cat></item><item><cat>W</cat></item><item><cat>B</cat></item></items>

For more information, see SQL Server to PostgreSQL Migration.