[SQL] SQL/XML Multi table join question

[Mina R Waheeb]

Hi all,
   I have the following tables (parent and two children)

CREATE SEQUENCE person_seq;
CREATE TABLE person(
        _id integer DEFAULT nextval('person_seq') NOT NULL,
        _timestamp TIMESTAMP NOT NULL,
        _lastModified TIMESTAMP NOT NULL,
        name VARCHAR(255) NOT NULL,
        age INTEGER DEFAULT NULL,
        PRIMARY KEY (_id)
);
CREATE TABLE person_nationality(
        _id serial NOT NULL,
        _parent INTEGER NOT NULL,
        nationality VARCHAR(255) NOT NULL,
        FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,
        PRIMARY KEY (_id)
);
CREATE TABLE person_variables(
        _id serial NOT NULL,
        _parent INTEGER NOT NULL,
        variable VARCHAR(255) DEFAULT NULL,
        value VARCHAR(255) DEFAULT NULL,
        FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,
        PRIMARY KEY (_id)
);

I'm trying to generate XML element for each person which also contains
the person nationality and variables in one result set

SELECT
XMLROOT (
  XMLELEMENT (
    NAME information,
    XMLATTRIBUTES (
      person._id AS pid
    ),
        XMLAGG(
                XMLELEMENT(
                        name "nationality",
                        person_nationality.nationality
                )
        ),
        XMLAGG(
                XMLELEMENT(
                        name "value",
                        person_variables.value
                )
        )
  ),
  VERSION '1.0',
  STANDALONE YES
)
FROM person
INNER JOIN person_nationality ON person_nationality._parent = person._id
INNER JOIN person_variables ON person_variables._parent = person._id
GROUP BY person._id
LIMIT 100 OFFSET 10000;

The above query return number of variables * nationality for each
person which is expected (for me) because of the join logic. Also i
can't use DISTINCT keyword in XMLAGG function.

Any idea how to do this? or is there is any aggregate function returns
array from row set, i didn't find any in the documenation.

Thanks in advance

Mina.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql