CODEWITHSUNDEEP
sql-serverpostgresqlxpath
Anduril28
Anduril28

Reputation: 313

Postgres OpenXML

To start this off: Yes, I've read this post regarding OpenXML and postgres.

  1. Is this still the case 6 years later?

  2. If so, my company is converting from MS SQL Server to Postgres, with A LOT of stored procedures, many of which really rely on the OpenXML. It seems pretty cumbersome to have to write out a xpath() for every possible thing would would like to retrieve from our XML. Is there any recommendations you have to go about this conversion? An better alternative to xpath() that I haven't seen, yet?

Thanks!

Upvotes: 4

Views: 428

Answers (2)

alex
alex

Reputation: 11

Analog OPENXML in PostgreSQL (hoping that the nodes are viewed by xmltable in the order they appear in the input xml):

    CREATE OR REPLACE FUNCTION openxml (
      p_xml xml
    )
    RETURNS TABLE (
      id integer,
      parent_id integer,
      element_name text,
      element_data text
    ) AS
    $body$
    DECLARE
    BEGIN
      return query
      with t as (SELECT *
                 from xmltable('//*' 
                               passing p_xml           
                               columns 
                                 id for ORDINALITY,
                                 element_name text path 'local-name()',
                                 parent_name text path 'local-name(..)',
                                 element_data text path 'text()[1]'
                               ))
      select t.id, 
            (select max(t1.id) from t t1 where t1.id<t.id and t.parent_name=t1.element_name)as parent_id, 
            t.element_name, 
            t.element_data
      from t;                                  

END;
$body$
LANGUAGE 'plpgsql';

Upvotes: 1

Anduril28
Anduril28

Reputation: 313

After much research:

1) Yes, it appears so.

2) Yeah, it is kind of a pain, made an internal program that grabs potential XML paths and creates a script for us requiring only a few tweaks by hand.

Thanks

Upvotes: 2

Related Questions