Using TSQL and For XML Path to generate XML output

Question

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>

Can you provide some sample (sanitized) data for your temp tables? — Dan Field
– Dan Field, Commented Mar 21, 2017 at 21:11
Hi Dan thanks, the queries are huge so I wont paste them here but, essentially they populate the 3 tables — jimmy8ball
– jimmy8ball, Commented Mar 21, 2017 at 21:17
If it helps temp is MAT003, temp2 is MAT001 and temp3 is MAT101 and the tables match the schema in the first block of XML — jimmy8ball
– jimmy8ball, Commented Mar 21, 2017 at 21:20
You don't have to add the queries - just some sample data is enough (INSERT statemnts into the temp tables would be great or SELECT .. INTO)... — Dan Field
– Dan Field, Commented Mar 21, 2017 at 21:25
INSERT INTO #temp ( [LocalPatientIdMother], [OrgCodeLocalPatientIdMother], [OrgCodeRes], [NHSNumberMother], [NHSNumberStatusMother], [PersonBirthDateMother], [Postcode], [EthnicCategoryMother], [PersonDeathDateTimeMother] ) VALUES ('1112222', 'BB', 'GUG', '111111111111', '1', '1900-01-01', 'HA3 TAW', 'Z', '') — jimmy8ball
– jimmy8ball, Commented Mar 21, 2017 at 21:38

Nimantha · Accepted Answer · 2023-11-12 18:06:44Z

1

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...

edited Nov 12, 2023 at 18:06

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Mar 22, 2017 at 7:50

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using TSQL and For XML Path to generate XML output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related