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?

CodePudding user response：

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;