DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=V9onHap1QTR0K+7HlW8kI6AWgA8pY5X6wMtrNdJLm8QXUULe3hg1+3Z7UwuA9ThDjTGrpA2zmnXqV2k/qUC/0Zbb2itVPWQs+gfwjlq9TZ9aZzflUgM94x/As6P2FhR6ud+nX8vlhRqxkdUIGX8kyfabITYWRInOIcMWlrTbXTQ=
Message-ID: <275890790804161134k2921148dy60206d890a047d46@mail.gmail.com>
Date: Wed, 16 Apr 2008 20:34:43 +0200
From: "Mina R Waheeb" <syncer@gmail.com>
To: pgsql-sql@postgresql.org
Subject: SQL/XML Multi table join question
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.