Postgres view via iterating with loop through xml

Question

First of all, I have following xml:

<?xml version="1.0" encoding="UTF-8"?>
   <control-sygnatura>XTW/GAT/GTW/1/00007</control-sygnatura>
   <control-inst>Firma 1</control-inst>
   <section-uczestnicy fr:section-status="changed">
      <section-uczestnicy-iteration>
         <section-uczestnik>
            <section-dane-uczestnika>
               <control-imie>Piotr</control-imie>
               <control-nazwisko>Pawel</control-nazwisko>
            </section-dane-uczestnika>
         </section-uczestnik>
      </section-uczestnicy-iteration>
      <section-uczestnicy-iteration>
         <section-uczestnik>
            <section-dane-uczestnika>
               <control-imie>Izabella</control-imie>
               <control-nazwisko>Agata</control-nazwisko>
            </section-dane-uczestnika>
         </section-uczestnik>
      </section-uczestnicy-iteration>
   </section-uczestnicy>
 </xml>

and I need to create view from this xml which contains 4 columns control-sygnatura, control-inst, control-imie and control-nazwisko.

I wrote following postgresql script:

CREATE OR REPLACE VIEW public.exportSL
 AS SELECT 
  btrim(xpath('//control-sygnatura/text()'::text, ofd.xml)::text, '{}'::text) AS control-sygnatura,
  btrim(xpath('//control-inst/text()'::text, ofd.xml)::text, '{}'::text)      AS control-inst,
  btrim(xpath('//control-imie/text()'::text, ofd.xml)::text, '{}'::text)      AS control-imie,
  btrim(xpath('//control-nazwisko/text()'::text, ofd.xml)::text, '{}'::text)  AS control-nazwisko,
 FROM orbeon_form_data ofd

But the problem is that my view have records

control-sygnatura      control-inst       control-imie     control-nazwisko
___________________________________________________________________________
XTW/GAT/GTW/1/00007    Firma 1            Piotr, Izabela   Pawel, Agata   
XTW/GAT/GTW/1/00007    Firma 1            Piotr, Izabela   Pawel, Agata

instead of (one person from in one record )

control-sygnatura      control-inst       control-imie     control-nazwisko
___________________________________________________________________________
XTW/GAT/GTW/1/00007    Firma 1            Piotr            Pawel   
XTW/GAT/GTW/1/00007    Firma 1            Izabela          Agata

Do You have any idea how to do it in postgres?

Stefanov.sm · Accepted Answer · 2022-10-21 12:53:51Z

2

Use XMLTABLE. Please note that your xml was invalid (missing root tag) and namespace fr: is not defined. I have added <xml> root tag and removed fr:. orbeon_form_data CTE with only one column is a mimic of your actual table.

with orbeon_form_data(xmlf) as 
(
 values 
 (
 '<?xml version="1.0" encoding="UTF-8"?>
  <xml>
  <control-sygnatura>XTW/GAT/GTW/1/00007</control-sygnatura>
  <control-inst>Firma 1</control-inst>
  <section-uczestnicy section-status="changed">
     <section-uczestnicy-iteration>
        <section-uczestnik>
           <section-dane-uczestnika>
              <control-imie>Piotr</control-imie>
              <control-nazwisko>Pawel</control-nazwisko>
           </section-dane-uczestnika>
        </section-uczestnik>
     </section-uczestnicy-iteration>
     <section-uczestnicy-iteration>
        <section-uczestnik>
           <section-dane-uczestnika>
              <control-imie>Izabella</control-imie>
              <control-nazwisko>Agata</control-nazwisko>
           </section-dane-uczestnika>
        </section-uczestnik>
     </section-uczestnicy-iteration>
   </section-uczestnicy>
  </xml>'::xml
 )
) 
select xmlt.* 
from orbeon_form_data 
cross join lateral
XMLTABLE
(
 '/xml/section-uczestnicy/section-uczestnicy-iteration/section-uczestnik/section-dane-uczestnika'
 PASSING by ref xmlf
 COLUMNS
  "control-sygnatura" text path '/xml/control-sygnatura',
  "control-inst" text path '/xml/control-inst',
  "control-imie" text PATH 'control-imie',
  "control-nazwisko" text PATH 'control-nazwisko'
) xmlt;

control-sygnatura	control-inst	control-imie	control-nazwisko
XTW/GAT/GTW/1/00007	Firma 1	Piotr	Pawel
XTW/GAT/GTW/1/00007	Firma 1	Izabella	Agata

edited Oct 21, 2022 at 12:53

answered Oct 21, 2022 at 12:43

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

2 Comments

Ionuț G. Stan Over a year ago

I've never worked with XMLTABLE, so I had a look at the docs and it seems that BY REF is not implemented. The mode always being BY VALUE, even if BY REF is specified: postgresql.org/docs/15/…

Stefanov.sm Over a year ago

You are right. Anyway, whenever and if it gets implemented in the future then the query might become a bit more efficient.

Collectives™ on Stack Overflow

Postgres view via iterating with loop through xml

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related