Is there any equivalent of Oracle's XMLQUERY in PostgreSQL?

[Burma]

I'm trying to find equivalent of XMLQuery in PostgreSQL.

Following results I got from Oracle's https://livesql.oracle.com/ . Please suggest how to get same results on PostgreSQL.

CREATE TABLE person_data (
person_id   NUMBER(3),
person_data XMLTYPE);
INSERT INTO person_data

(person_id, person_data)

VALUES

(1, XMLTYPE('

Daniel Morgan

12/1/1951

damorgan@u.washington.edu

')

);
INSERT INTO person_data

(person_id, person_data)

VALUES

(2, XMLTYPE('

Taner Poder

5/17/1949

damorgan@u.washington.edu

')

);
SELECT person_id,

XMLQuery('for $i in /PDRecord

return $i/PDName'

PASSING BY VALUE person_data

RETURNING CONTENT) XMLData

FROM person_data;

Output:

 PERSON_ID  XMLDATA
1      Daniel Morgan

2      Taner Poder

[juvenalb]

You can use the xpath() function:

select pd.person_id, 
       (xpath('/PDRecord/PDName', pd.person_data))[1]
from person_data pd;

This returns

person_id | xpath                         
----------+-------------------------------
        1 | Daniel Morgan
        2 | Taner Poder  

xpath() returns an array of matches. [1] then picks the first element of that array.

If you want the raw content of the tag, then xmltable() is more suitable as it's also easier to add more columns:

select pd.person_id, 
       x.*
from person_data pd
  cross join xmltable('/PDRecord' passing pd.person_data
                      columns name text path 'PDName', 
                              email text path 'PDEmail')  as x

This returns:

person_id | name          | email                    
----------+---------------+--------------------------
        1 | Daniel Morgan | damorgan@u.washington.edu
        2 | Taner Poder   | damorgan@u.washington.edu

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9eace732300305ecc1146cdc1bf9d07b