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