Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customer (
    cust_id int,
    name text,
    country text
);

INSERT INTO customer VALUES 
(1, 'Batman', 'GC'),
(2, 'Superman', 'MC');

CREATE TABLE games (
    game_id int,
    name text,
    price numeric
);

INSERT INTO games VALUES 
(1, 'Kill the Joker', 100.00),
(2, 'Kill Lex Luther', 1.42),
(3, 'Kill them all', 42.00);

CREATE TABLE download (
    cust_id int,
    game_id int,
    version text
);

INSERT INTO download VALUES
(1, 1, '0.2 Beta'),
(1, 3, '23.1a'),
(2, 2, 'Trial 3'),
(2, 3, '20.1-42');
2 rows affected

3 rows affected

4 rows affected

SELECT *
FROM
    customer c
JOIN
    download d ON (c.cust_id = d.cust_id)
JOIN
    games g ON (d.game_id = g.game_id)


cust_id
name
country
cust_id
game_id
version
game_id
name
price


1
Batman
GC
1
1
0.2 Beta
1
Kill the Joker
100.00

2
Superman
MC
2
2
Trial 3
2
Kill Lex Luther
1.42

1
Batman
GC
1
3
23.1a
3
Kill them all
42.00

2
Superman
MC
2
3
20.1-42
3
Kill them all
42.00


SELECT 
    xmlelement(name customers, 
        xmlagg(
            xmlelement(name customer, 
                xmlforest(cust_id, name, country, games)
            )
        )
    )
FROM (
    SELECT
        c.cust_id,
        c.name,
        c.country,
        xmlelement(name games, 
            xmlagg(
                xmlelement(name game, 
                    xmlforest(g.game_id, g.name, price)
                )
            )
        ) as games
    FROM
        customer c
    JOIN
        download d ON (c.cust_id = d.cust_id)
    JOIN
        games g ON (d.game_id = g.game_id)
    GROUP BY c.cust_id, c.name, c.country
    ORDER BY c.cust_id
) s


xmlelement


<customers><customer><cust_id>1</cust_id><name>Batman</name><country>GC</country><games><games><game><game_id>1</game_id><name>Kill the Joker</name><price>100.00</price></game><game><game_id>3</game_id><name>Kill them all</name><price>42.00</price></game></games></games></customer><customer><cust_id>2</cust_id><name>Superman</name><country>MC</country><games><games><game><game_id>2</game_id><name>Kill Lex Luther</name><price>1.42</price></game><game><game_id>3</game_id><name>Kill them all</name><price>42.00</price></game></games></games></customer></customers>

cust_id	name	country	cust_id	game_id	version	game_id	name	price
1	Batman	GC	1	1	0.2 Beta	1	Kill the Joker	100.00
2	Superman	MC	2	2	Trial 3	2	Kill Lex Luther	1.42
1	Batman	GC	1	3	23.1a	3	Kill them all	42.00
2	Superman	MC	2	3	20.1-42	3	Kill them all	42.00