Hi All,

I'm using DB2 v9 Express-C - I have problem when selecting XML
columns.

I have 2 columns in patients table:
- id_patient
- patient_info

select * from patients;
select id_patient, patient_info from patients;

work fine - but that's easy ;)

I added new table examinations (1 patient can have many examinations)
examination:
id_examination
id_patient
examination_results

and tried to do something like this, show me patients' ids and infos +
number of examinations:

SELECT P.ID_PATIENT, P.PATIENT_INFO, COUNT(E.ID_EXAMINATION) AS
EXAMINATION#
 FROM PATEINTS as P, EXAMINATIONS as E
 WHERE P.ID_PATIENT = E.ID_PATIENT
 GROUP BY P.ID_PATIENT;

but above query returns an error - P.PATIENT_INFO is not included in
"group by", but when
I added it, there was another error saying that XML cannot be used in
grouping functions

any idea what should I do to fetch pateint_info XML + number of
examinations in one query?
does DB2 supports such things?

I have written query with subquery that produces me expected results
- but I'm wondering whether it can be done faster and in more
beautiful way ;)

thanks in advance for any tips
best regards
R