Postgres XMLCONCAT() with subqueries

Question

I am trying to use Postgres to generate a single XML document as output.

Assume that I have a single relation, customers. It has the columns id, name and email.

I want XML that looks like this:

<customers>
    <customer>
        <id>0<id/>
        <name>Customer 1<name/>
        <email>customer1@gmail.com<email/>
    <customer/>
    <customer>
        <id>1<id/>
        <name>Customer 2<name/>
        <email>customer2@gmail.com<email/>
    <customer/>
    <!--and so on-->
<customers/>

So far, I can generate the XML for each customer, like this:

SELECT XMLELEMENT(NAME customer, 
           XMLELEMENT(NAME 'id', "id"),
           XMLELEMENT(NAME 'name', name), 
           XMLELEMENT(NAME email, email))
FROM customers;

However, concatenating this into a single XML document does not work. I have tried:

SELECT XMLELEMENT(NAME customers, XMLCONCAT((
    SELECT XMLELEMENT(NAME customer, 
               XMLELEMENT(NAME 'id', "id"),
               XMLELEMENT(NAME 'name', name), 
               XMLELEMENT(NAME email, email))
FROM customers;
)));

However, this fails with the message more than one row returned by a subquery used as an expression.

You need to aggregate them. Since xmlagg is slow, you could do something like `SELECT XMLELEMENT(NAME customers, STRING_AGG(XMLELEMENT(NAME customer, XMLELEMENT(NAME 'id', "id"), ...)::TEXT, ',')::XML) FROM customers` — 404, Jan 15 '20 at 15:08
Refer to this link for the solution https://stackoverflow.com/questions/21048955/postgres-error-more-than-one-row-returned-by-a-subquery-used-as-an-expression — Sola Oshinowo, Jan 15 '20 at 15:20

Jim Jones · Answer 1 · 2020-01-15T16:15:32.250

There are many ways of achieving this. An easy approach is to fetch the XML elements you want in a CTE and aggregate them with xmlagg

Sample Data

CREATE TEMPORARY TABLE customers (id int, name text, email text);
INSERT INTO customers VALUES 
(0,'Customer 1','customer1@gmail.com'),
(0,'Customer 2','customer2@gmail.com');

Query

WITH j AS (
  SELECT 
    XMLELEMENT(NAME customer, 
      XMLELEMENT(NAME id, id),
      XMLELEMENT(NAME name, name), 
      XMLELEMENT(NAME email, email)) AS customers
  FROM customers)
SELECT 
  XMLELEMENT(NAME customers, xmlagg(customers)) 
FROM j;

------------
<customers>
    <customer>
        <id>0</id>
        <name>Customer 1</name>
        <email>customer1@gmail.com</email>
    </customer>
    <customer>
        <id>0</id>
        <name>Customer 2</name>
        <email>customer2@gmail.com</email>
    </customer>
</customers>

Another alternative is to use a subquery

SELECT 
  XMLELEMENT(NAME customers, xmlagg((customers)))
FROM (SELECT 
  XMLELEMENT(NAME customer, 
    XMLELEMENT(NAME id, id),
    XMLELEMENT(NAME name, name), 
    XMLELEMENT(NAME email, email)) AS customers
FROM customers) j;

score 0 · Answer 2 · answered Jan 15 '20 at 15:24

0

Add LIMIT 1 to the Subquery. This will remove the duplicate rows.

answered Jan 15 '20 at 15:24

Sola Oshinowo

519
4
13

Postgres XMLCONCAT() with subqueries

2 Answers2