• 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;