sqlsql-serverxmlsql-server-2008t-sql

Using TSQL and For XML Path to generate XML output


I have 3 temp tables all populated by 3 independent queries and are associated to each other with a 1 to 1 relationship, these tables are DemographicRecord, GPRegistrationDetails, MaternityBookingDetails. The columns are different between all 3 but each share the PatientID key. My question is using XML Path how can I output XML from the 3 related datasets following the format below.

    <MAT001MothersDemographics>
        <LocalPatientIdMother>BLANKED</LocalPatientIdMother>
        <OrgCodeLocalPatientIdMother>BLANKED</OrgCodeLocalPatientIdMother>
        <OrgCodeRes>BLANKED</OrgCodeRes>
        <NHSNumberMother>BLANKED</NHSNumberMother>
        <NHSNumberStatusMother>BLANKED</NHSNumberStatusMother>
        <PersonBirthDateMother>BLANKED</PersonBirthDateMother>
        <Postcode>BLANKED</Postcode>
        <EthnicCategoryMother>BLANKED</EthnicCategoryMother>
        <PersonDeathDateTimeMother>BLANKED</PersonDeathDateTimeMother>
        <MAT003GPPracticeRegistration>
            <LocalPatientIdMother>BLANKED</LocalPatientIdMother>
            <OrgCodeGMPMother>BLANKED</OrgCodeGMPMother>
            <StartDateGMPRegistration>BLANKED</StartDateGMPRegistration>
            <EndDateGMPRegistration>BLANKED</EndDateGMPRegistration>
            <OrgCodeCommissioner>BLANKED</OrgCodeCommissioner>
        </MAT003GPPracticeRegistration>
        <MAT101BookingAppointmentDetails>
            <AntenatalAppDate>BLANKED</AntenatalAppDate>
            <LocalPatientIdMother>BLANKED</LocalPatientIdMother>
            <EDDAgreed>BLANKED</EDDAgreed>
            <EDDMethodAgreed>BLANKED</EDDMethodAgreed>
            <PregnancyFirstContactDate>BLANKED</PregnancyFirstContactDate>
            <PregnancyFirstContactCareProfessionalType>BLANKED</PregnancyFirstContactCareProfessionalType>
            <LastMenstrualPeriodDate>BLANKED</LastMenstrualPeriodDate>
            <PhysicalDisabilityStatusIndMother>BLANKED</PhysicalDisabilityStatusIndMother>
            <FirstLanguageEnglishIndMother>BLANKED</FirstLanguageEnglishIndMother>
            <EmploymentStatusMother>BLANKED</EmploymentStatusMother>
            <SupportStatusMother>BLANKED</SupportStatusMother>
            <EmploymentStatusPartner>BLANKED</EmploymentStatusPartner>
            <PreviousCaesareanSections>BLANKED</PreviousCaesareanSections>
            <PreviousLiveBirths>BLANKED</PreviousLiveBirths>
            <PreviousStillBirths>BLANKED</PreviousStillBirths>
            <PreviousLossesLessThan24Weeks>BLANKED</PreviousLossesLessThan24Weeks>
            <SubstanceUseStatus>BLANKED</SubstanceUseStatus>
            <SmokingStatus>BLANKED</SmokingStatus>
            <CigarettesPerDay>BLANKED</CigarettesPerDay>
            <AlcoholUnitsPerWeek>BLANKED</AlcoholUnitsPerWeek>
            <FolicAcidSupplement>BLANKED</FolicAcidSupplement>
            <MHPredictionDetectionIndMother>BLANKED</MHPredictionDetectionIndMother>
            <PersonWeight>BLANKED</PersonWeight>
            <PersonHeight>BLANKED</PersonHeight>
            <ComplexSocialFactorsInd>BLANKED</ComplexSocialFactorsInd>
        </MAT101BookingAppointmentDetails>
</MAT001MothersDemographics>

So far I have tried:

SELECT
(SELECT * FROM #temp2 
JOIN #temp ON #temp2.LocalPatientIdMother = #temp.LocalPatientIdMother
JOIN #temp3 ON #temp2.LocalPatientIdMother = #temp3.LocalPatientIdMother
FOR XML PATH('MAT001'), TYPE) AS 'MAT001MothersDemographics' 
FOR XML PATH(''), ROOT('root')

But this is not the correct shape, can someone advise how I can use TSQL and FOR XML PATH effectively so I can generate the above output? I am currently getting the demographics repeated for every record before the other data is displayed?

    <MAT001MothersDemographics>
     <MAT001>
      <LocalPatientIdMother>BLANKED</LocalPatientIdMother>
      <OrgCodeLocalPatientIdMother>BLANKED</OrgCodeLocalPatientIdMother>
      <OrgCodeRes>BLANKED</OrgCodeRes>
      <NHSNumberMother>BLANKED</NHSNumberMother>
      <NHSNumberStatusMother>BLANKED</NHSNumberStatusMother>
      <PersonBirthDateMother>BLANKED</PersonBirthDateMother>
      <Postcode>BLANKED</Postcode>
      <EthnicCategoryMother>BLANKED</EthnicCategoryMother>
      <PersonDeathDateTimeMother>BLANKED</PersonDeathDateTimeMother>
      </MAT001>
    </MAT001MothersDemographics>
    <MAT001MothersDemographics>
     <MAT001>
      <LocalPatientIdMother>BLANKED</LocalPatientIdMother>
      <OrgCodeLocalPatientIdMother>BLANKED</OrgCodeLocalPatientIdMother>
      <OrgCodeRes>BLANKED</OrgCodeRes>
      <NHSNumberMother>BLANKED</NHSNumberMother>
      <NHSNumberStatusMother>BLANKED</NHSNumberStatusMother>
      <PersonBirthDateMother>BLANKED</PersonBirthDateMother>
      <Postcode>BLANKED</Postcode>
      <EthnicCategoryMother>BLANKED</EthnicCategoryMother>
      <PersonDeathDateTimeMother>BLANKED</PersonDeathDateTimeMother>
      </MAT001>
    </MAT001MothersDemographics>

Solution

  • I try a quick shot:

    SELECT t.*
          ,(
             SELECT * 
             FROM #temp2 AS t2 
             WHERE t.LocalPatientIdMother=t2.LocalPatientIdMother
             FOR XML PATH('MAT003GPPracticeRegistration'),TYPE
           ) AS [*]
          ,(
             SELECT * 
             FROM #temp3 AS t3 
             WHERE t.LocalPatientIdMother=t3.LocalPatientIdMother
             FOR XML PATH('MAT101BookingAppointmentDetail'),TYPE
           ) AS [*]
    FROM #temp AS t
    FOR XML PATH('MAT001MothersDemographics');
    

    This will return all columns of #temp1 and will nest the related rows of #temp2 and #temp3. This is based on the assumption, that you have one record for the given ID in each table only...