0

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

Following results I got from Oracle's LiveSql. 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('<PDRecord>
     <PDName>Daniel Morgan</PDName>
     <PDDOB>12/1/1951</PDDOB>
     <PDEmail>[email protected]</PDEmail>
 </PDRecord>')
);

INSERT INTO person_data
(person_id, person_data)
VALUES
(2, XMLTYPE('<PDRecord>
     <PDName>Taner Poder</PDName>
     <PDDOB>5/17/1949</PDDOB>
     <PDEmail>[email protected]</PDEmail>
   </PDRecord>')
);

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      <PDName>Daniel Morgan</PDName>
 2      <PDName>Taner Poder</PDName>

1 Answer 1

2

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 | <PDName>Daniel Morgan</PDName>
        2 | <PDName>Taner Poder</PDName>  

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 | [email protected]
        2 | Taner Poder   | [email protected]

Online example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.