I got it. Nested XML that's the right answer.

Thanks for all your help.

Final solution:

select

'04' as "@PaediatricCriticalCareStructure",

p.CriticalCareLocalIdentifier,

p.CriticalCareStartDate,

(select

p.ActivityDate,

'',

p.CriticalCareActivityCode1 as CriticalCareActivityCode,

'',

p.CriticalCareActivityCode2 as CriticalCareActivityCode,

'',

p.CriticalCareActivityCode3 as CriticalCareActivityCode,

'',

p.CriticalCareActivityCode4 as CriticalCareActivityCode,

'',

p.CriticalCareActivityCode5 as CriticalCareActivityCode,

'',

p.CriticalCareActivityCode6 as CriticalCareActivityCode

from

#picu p1

where

p.CriticalCareLocalIdentifier = p1.CriticalCareLocalIdentifier

for

xml path('PCC_CareActivityGroup'),

type,

elements)

from

#picu p