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



  • 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



  • 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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2