# Xml in postgresql

**URL:** <https://www.pg-forum.de/t/xml-in-postgresql/12992>\
**Category:** Unkategorisiert\
**Created:** [8. April 2024 um 13:51 UTC](https://www.pg-forum.de/t/xml-in-postgresql/12992 "2024-04-08T13:51:11Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![flight](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/f/bbce88/32.png) [@flight](https://www.pg-forum.de/u/flight)\
**Post date:** [8. April 2024 um 13:51 UTC](https://www.pg-forum.de/t/xml-in-postgresql/12992/1 "2024-04-08T13:51:12Z")

</div>

Oracle Code:

v\_select\_query := ‘select XMLAGG (XMLELEMENT (e, acct\_group, ‘’,’‘).  
EXTRACT (’‘//text()’‘)).getclobval ()  
from (select distinct trim(ic\_acct\_group\_702) acct\_group  
from investment\_code,gl\_skat,client\_entity  
where ic\_acct\_group\_702 = trim(skat\_group(+))  
and CLIENT\_ID\_208 = ic\_client\_id\_702(+)  
and nvl(ic\_active\_flag\_702, ‘‘A’’) \<\> ‘‘I’’  
and ic\_no\_702 in (select ic\_no\_702 from investment\_code  
start with ic\_no\_702 in (select connection\_ic\_no\_694 from personnel\_ic\_connector  
where connection\_pers\_code\_694 = ‘’’|| v\_user\_code ||‘’‘)  
connect by prior ic\_no\_702 = ic\_owner\_no\_702)’;

Migrated postgresql code:

ERROR: assignment source returned 3 columns

v\_select\_query:=’

SELECT RTRIM(string\_agg(xpath(concat(‘‘rowset’’,‘’/c’‘, ‘’/text()’‘), XMLELEMENT  
(NAME rowset, XMLAGG(XMLELEMENT (NAME e, CONCAT\_WS(’‘, acct\_group, ‘,’))))), ‘’)::XML::TEXT, ‘,’)  
AS acct\_group  
(select distinct trim(ic\_acct\_group\_702) acct\_group  
from investment\_code left join gl\_skat  
on ic\_acct\_group\_702 = trim(skat\_group)  
left join client\_entity on  
CLIENT\_ID\_208 = ic\_client\_id\_702  
and coalesce(ic\_active\_flag\_702, ‘‘A’’) \<\> ‘‘I’’  
and ic\_no\_702 in  
(  
WITH RECURSIVE hierarchy\_cte AS (  
SELECT  
ic\_no\_702,  
ic\_owner\_no\_702  
FROM investment\_code  
WHERE ic\_no\_702 IN (  
SELECT connection\_ic\_no\_694  
FROM personnel\_ic\_connector  
WHERE connection\_pers\_code\_694 = ‘’’|| v\_user\_code ||‘’’  
)  
UNION ALL  
SELECT  
ic.ic\_no\_702,  
ic.ic\_owner\_no\_702  
FROM hierarchy\_cte hc  
JOIN investment\_code ic ON hc.ic\_no\_702 = ic.ic\_owner\_no\_702  
)  
SELECT ic\_no\_702  
FROM hierarchy\_cte)';

---

<div class="post-metadata">

**Author:** ![castorp](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/c/e99b99/32.png) [@castorp](https://www.pg-forum.de/u/castorp)\
**Post date:** [8. April 2024 um 17:36 UTC](https://www.pg-forum.de/t/xml-in-postgresql/12992/2 "2024-04-08T17:36:10Z")

</div>

Warum xmlagg? Was genau enthält die Spalte `act_group`? Was soll als Ausgabe erfolgen.

Ohne Beispieldaten und erwünschtes Ergebnis ist es praktisch unmöglich diese Frage zu beantworten.

---

<div class="post-metadata">

**Author:** ![system](https://www.pg-forum.de/uploads/default/original/1X/dc97a981dd26692d5fb52e8b8fc7c090d7b9559a.png) [@system](https://www.pg-forum.de/u/system)\
**Post date:** [7. Juni 2024 um 17:36 UTC](https://www.pg-forum.de/t/xml-in-postgresql/12992/3 "2024-06-07T17:36:39Z")

</div>

Geschlossen, am 7. Juni 2024 um 17:36 UTC

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.
