Home Forums SQL Server 2008 SQL Server 2008 - General Subject: XML from SQL table (for xml explicit or namespaces?) RE: Subject: XML from SQL table (for xml explicit or namespaces?)

  • January 16, 2013 at 10:01 am

    #1577936

    Think I got in now (see the code below).

    It's going to be painful to build dynamic pivot / SQL for the purpose of this task.

    Any other solutions anyone?

    Mike

    select distinct

    1 as Tag,

    null as Parent,

    '04' as [PaediatricCriticalCarePeriod!1!PaediatricCriticalCareStructure],

    null as [PCC_AdmissionCharacteristics!2!CriticalCareLocalIdentifier!ELEMENT],

    null as [PCC_AdmissionCharacteristics!2!CriticalCareStartDate!ELEMENT],

    null as [PCC_CareActivityGroup!3!ActivityDate!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode1!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode2!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode3!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode4!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode5!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode6!ELEMENT]

    from

    #picu CriticalCarePeriod

    union all

    select distinct

    2 as Tag,

    1 as Parent,

    '04',

    CriticalCarePeriod.CriticalCareLocalIdentifier,

    CriticalCarePeriod.CriticalCareStartDate,

    null as [PCC_CareActivityGroup!3!ActivityDate!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode1!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode2!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode3!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode4!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode5!ELEMENT],

    null as [PCC_CareActivityGroup!3!CriticalCareActivityCode6!ELEMENT]

    from

    #picu CriticalCarePeriod

    union all

    select distinct

    3 as Tag,

    1 as Parent,

    '04',

    CriticalCarePeriod.CriticalCareLocalIdentifier,

    CriticalCarePeriod.CriticalCareStartDate,

    CriticalCarePeriod.ActivityDate,

    CriticalCarePeriod.CriticalCareActivityCode1,

    CriticalCarePeriod.CriticalCareActivityCode2,

    CriticalCarePeriod.CriticalCareActivityCode3,

    CriticalCarePeriod.CriticalCareActivityCode4,

    CriticalCarePeriod.CriticalCareActivityCode5,

    CriticalCarePeriod.CriticalCareActivityCode6

    from

    #picu CriticalCarePeriod

    order by

    [PCC_AdmissionCharacteristics!2!CriticalCareLocalIdentifier!ELEMENT],

    [PCC_AdmissionCharacteristics!2!CriticalCareStartDate!ELEMENT],

    [PCC_CareActivityGroup!3!ActivityDate!ELEMENT]

    for xml explicit;