Thanks again.
Nearly there. Still got a problem to put together in one group: ActivityDate & CriticalCareActivityCode
See the code below.
Mike
SELECT
'04' as "@PaediatricCriticalCareStructure",
p.CriticalCareLocalIdentifier as "PCC_AdmissionCharacteristics/CriticalCareLocalIdentifier",
p.CriticalCareStartDate as "PCC_AdmissionCharacteristics/CriticalCareStartDate",
p.ActivityDate as "PCC_CareActivityGroup/ActivityDate",
'',
p.CriticalCareActivityCode1 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode2 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode3 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode4 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode5 as "PCC_CareActivityGroup/CriticalCareActivityCode",
'',
p.CriticalCareActivityCode6 as "PCC_CareActivityGroup/CriticalCareActivityCode"
FROM #picu p
FOR XML PATH('PaediatricCriticalCarePeriod'),
TYPE;