Querying XML Data - SQL Server to PostgreSQL Migration

In SQL Server you can use a NODES method of XML data type to convert XML items into table rows. In PostgreSQL you can use XPATH and UNNEST functions.

Note that unlike SQL Server, PostgreSQL XML data must be a well formed XML document. In particular, it must have a single root node element.

SQL Server:

  -- Sample XML data, the root node is optional (!)
  DECLARE @data XML = '<root><a>1</a><a>2</a><a>3</a></root>'
 
  -- Get XML data as rows
  SELECT t.value('.', 'varchar(255)') AS item
  FROM @data.nodes('//a') AS x(t);
  # 1
  # 2
  # 3

PostgreSQL:

  -- The root node is required (!)
  SELECT unnest(xpath('//a/text()', '<root><a>1</a><a>2</a><a>3</a></root>'::XML));
  # 1
  # 2
  # 3

For more information, see SQL Server to PostgreSQL Migration.