Обсуждение: SQL/XML Multi table join question

Поиск
Список
Период
Сортировка

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
TIMESTAMPNOT 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.