EXTRACT from XML Function - Oracle to PostgreSQL Migration

In Oracle the EXTRACT function allows you to extract content from XML using the specified XPATH expression.

In PostgreSQL use can use the XPATH function, but note that the order of parameters is different.

Oracle:

  SELECT EXTRACT(xml_doc, '/document/employee/text()')
  FROM
  (SELECT
     XMLTYPE('<?xml version="1.0"?><document>' ||
       XMLAGG(XMLTYPE('<employee>' || ename || '</employee>') order by ename) ||
       '</document>') AS xml_doc   
   FROM emp);
 
  # Result (single row):
  # ADAMSALLENBLAKECLARKFORDJAMESJONESKINGMARTINMILLERSCOTTSMITHTURNERWARD

PostgreSQL:

  SELECT XPATH('/document/employee/text()', xml_doc)
  FROM
  (SELECT
     XMLPARSE(DOCUMENT '<?xml version="1.0"?><document>' ||
       XMLAGG(XMLPARSE(CONTENT '<employee>' || ename || '</employee>') order by ename) ||
       '</document>') AS xml_doc   
   FROM emp) t;
 
  # Result (single row):
  # {ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD}

For more information, see Oracle to PostgreSQL Migration.