Postgres OpenXML

Question

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

Is this still the case 6 years later?
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!

I only mention this because you say your company is converting from MS SQL to Postgres: Are you aware that Enterprise DB is the for-profit part of Postgresql? They offer exactly the kind of support you might need for your company and they could easily provide the answer to this question for you. Not too many people realize it, so I figured I'd point it out. — AlVaz
– AlVaz, Commented Oct 7, 2015 at 15:08
It may have been considered at one point, but that decision was made higher up. So, for now, I'm looking for any advice on the above problems. Thanks, though! — Anduril28
– Anduril28, Commented Oct 7, 2015 at 15:17

Anduril28 · Accepted Answer · 2016-03-21 18:03:26Z

2

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

edited Mar 21, 2016 at 18:03

answered Feb 25, 2016 at 16:40

Anduril28

3131 gold badge4 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alex · Accepted Answer · 2021-07-22 11:59:09Z

1

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';

edited Jul 22, 2021 at 11:59

answered Jul 22, 2021 at 11:50

alex

112 bronze badges

2 Comments

user330315 Over a year ago

you don't actually need PL/pgSQL for this. A language sql function would be just fine.

alex Over a year ago

Of course, it is just an example to show the essence.

Collectives™ on Stack Overflow

Postgres OpenXML

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related