Попробовал новый постгрес, внушает
select
xmlroot(
xmlelement(name requests,
xmlagg(
xmlelement(name item,
xmlforest(cr.id,cr.user_id,u.name as user_name,f.name as firm_name),
(
select
xmlelement(name orders,
xmlagg(
xmlelement(name item,
xmlforest(cro.id,cro.item_name,cro.quantity,cro.lead_id,l.name as lead_name)
)
order by cro.id)
) as orders
from
Tcustomer_request_orders cro,
Tleads l
where
cro.request_id=cr.id and
cro.lead_id=l.id
)
)
order by cr.id desc)
),
version '1.0',
standalone yes
) as ret
from
Tcustomer_requests cr,
Tusers u,
Tfirms f
where
cr.user_id=u.id and
u.firm_id=f.idРезультат:
<?xml version="1.0" standalone="yes"?>
<requests>
<item>
<id>8</id><user_id>5</user_id><user_name>Маша</user_name><firm_name>Ромашка</firm_name>
<orders>
<item>
<id>13</id><item_name>MAX328</item_name><quantity>10</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
<item>
<id>14</id><item_name>MAX329</item_name><quantity>20</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
<item>
<id>15</id><item_name>MAX320</item_name><quantity>30</quantity><lead_id>1</lead_id><lead_name>?</lead_name>
</item>
</orders>
</item>
<item>
<id>7</id><user_id>5</user_id><user_name>Маша</user_name><firm_name>Ромашка</firm_name>
<orders>
<item>
<id>10</id><item_name>MAX324</item_name><quantity>60</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
<item>
<id>11</id><item_name>MAX323</item_name><quantity>70</quantity><lead_id>1</lead_id><lead_name>?</lead_name>
</item>
<item>
<id>12</id><item_name>MAX321</item_name><quantity>80</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
</orders>
</item>
<item>
<id>6</id><user_id>5</user_id><user_name>Маша</user_name><firm_name>Ромашка</firm_name>
<orders>
<item>
<id>9</id><item_name>MAX325</item_name><quantity>50</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
</orders>
</item>
<item>
<id>5</id><user_id>5</user_id><user_name>Маша</user_name><firm_name>Ромашка</firm_name>
<orders>
<item>
<id>8</id><item_name>MAX325</item_name><quantity>40</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
</orders>
</item>
<item>
<id>4</id><user_id>7</user_id><user_name>Вася</user_name><firm_name>ВП Система</firm_name>
<orders>
<item>
<id>7</id><item_name>MAX324</item_name><quantity>30</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
</orders>
</item>
<item>
<id>3</id><user_id>7</user_id><user_name>Вася</user_name><firm_name>ВП Система</firm_name>
<orders>
<item>
<id>6</id><item_name>MAX323</item_name><quantity>20</quantity><lead_id>3</lead_id><lead_name>lead-free</lead_name>
</item>
</orders>
</item>
<item>
<id>2</id><user_id>6</user_id><user_name>Петя</user_name><firm_name>Лютик</firm_name>
<orders>
<item>
<id>3</id><item_name>MAX325</item_name><quantity>90</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
<item>
<id>4</id><item_name>MAX326</item_name><quantity>90</quantity><lead_id>3</lead_id><lead_name>lead-free</lead_name>
</item>
<item>
<id>5</id><item_name>MAX328</item_name><quantity>10</quantity><lead_id>3</lead_id><lead_name>lead-free</lead_name>
</item>
</orders>
</item>
<item>
<id>1</id><user_id>5</user_id><user_name>Маша</user_name><firm_name>Ромашка</firm_name>
<orders>
<item>
<id>1</id><item_name>MAX323</item_name><quantity>50</quantity><lead_id>1</lead_id><lead_name>?</lead_name>
</item>
<item>
<id>2</id><item_name>MAX324</item_name><quantity>70</quantity><lead_id>2</lead_id><lead_name>lead</lead_name>
</item>
</orders>
</item>
</requests>
