none
How do I convert SQL into XML

    Question

  • Hi,

    The code below is a sample and what the end goal is regarding an output.

    Now assume all elements below will represented as SQL tables (MATHDRHeader, MAT001MothersDemographics .....etc).

    Is it possible to extract the data from the SQL tables to output exactly like the example below?

    <?xml version="1.0" encoding="UTF-8"?>
    
    <!--Sample XML file generated by XMLSpy v2013 rel. 2 sp2 (http://www.altova.com)-->
    
    -<MSDS:MSDS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:MSDS="http://www.datadictionary.nhs.uk/messages/MSDS-v1-0" xsi:schemaLocation="http://www.datadictionary.nhs.uk/messages/MSDS-v1-0 ../Schemas/MSDSMSDS_XMLSchema-v1-0.xsd">
    
    
    -<MATHDRHeader>
    <Version>1.0</Version>
    <OrgCodeProv>5BC</OrgCodeProv>
    <OrgCodeSubmitter>YEA</OrgCodeSubmitter>
    <RPStartDate>2013-01-01</RPStartDate>
    <RPEndDate>2013-03-12</RPEndDate>
    <FileCreationDateTime>2013-03-13T13:00:27</FileCreationDateTime>
    <RecordCount>1</RecordCount>
    
    
    -<MAT001MothersDemographics>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <OrgCodeLocalPatientIdMother>5BC</OrgCodeLocalPatientIdMother>
    <OrgCodeRes>5BC</OrgCodeRes>
    <NHSNumberMother>1111111111</NHSNumberMother>
    <NHSNumberStatusMother>01</NHSNumberStatusMother>
    <PersonBirthDateMother>1982-01-05</PersonBirthDateMother>
    <Postcode>LS1 4HY</Postcode>
    <EthnicCategoryMother>99</EthnicCategoryMother>
    <PersonDeathDateTimeMother>1900-01-01T00:00:00</PersonDeathDateTimeMother>
    
    
    -<MAT003GPPracticeRegistration>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <OrgCodeGMPMother>4RT</OrgCodeGMPMother>
    <StartDateGMPRegistration>2012-01-06</StartDateGMPRegistration>
    <EndDateGMPRegistration>1900-01-01</EndDateGMPRegistration>
    <OrgCodeCommissioner>6TY</OrgCodeCommissioner>
    </MAT003GPPracticeRegistration>
    
    
    -<MAT101BookingAppointmentDetails>
    <AntenatalAppDate>2013-03-01</AntenatalAppDate>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <EDDAgreed>2013-05-01</EDDAgreed>
    <EDDMethodAgreed>01</EDDMethodAgreed>
    <PregnancyFirstContactDate>2013-11-11</PregnancyFirstContactDate>
    <PregnancyFirstContactCareProfessionalType>060</PregnancyFirstContactCareProfessionalType>
    <LastMenstrualPeriodDate>2012-10-01</LastMenstrualPeriodDate>
    <PhysicalDisabilityStatusIndMother>Y</PhysicalDisabilityStatusIndMother>
    <FirstLanguageEnglishIndMother>Y</FirstLanguageEnglishIndMother>
    <EmploymentStatusMother>04</EmploymentStatusMother>
    <SupportStatusMother>Y</SupportStatusMother>
    <EmploymentStatusPartner>06</EmploymentStatusPartner>
    <PreviousCaesareanSections>0</PreviousCaesareanSections>
    <PreviousLiveBirths>0</PreviousLiveBirths>
    <PreviousStillBirths>0</PreviousStillBirths>
    <PreviousLossesLessThan24Weeks>0</PreviousLossesLessThan24Weeks>
    <SubstanceUseStatus>01</SubstanceUseStatus>
    <SmokingStatus>03</SmokingStatus>
    <CigarettesPerDay>0</CigarettesPerDay>
    <AlcoholUnitsPerWeek>0</AlcoholUnitsPerWeek>
    <FolicAcidSupplement>03</FolicAcidSupplement>
    <MHPredictionDetectionIndMother>N</MHPredictionDetectionIndMother>
    <PersonWeight>75.0</PersonWeight>
    <PersonHeight>1.45</PersonHeight>
    <ComplexSocialFactorsInd>N</ComplexSocialFactorsInd>
    
    
    -<MAT102ComplicatingMedicalDiagAtBooking>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <AntenatalAppDate>2013-03-01</AntenatalAppDate>
    <ComplicatingDiagTypeMother>01</ComplicatingDiagTypeMother>
    </MAT102ComplicatingMedicalDiagAtBooking>
    
    
    -<MAT103PreviousComplicatingObstetricDiagAtBooking>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <AntenatalAppDate>2013-03-01</AntenatalAppDate>
    <PreviousComplicatingDiagTypeMother>13</PreviousComplicatingDiagTypeMother>
    </MAT103PreviousComplicatingObstetricDiagAtBooking>
    
    
    -<MAT104FamilyHistoryDiagnosisAtBooking>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <AntenatalAppDate>2013-03-01</AntenatalAppDate>
    <MaternityFamilyHistoryDiagTypeAtBooking>07</MaternityFamilyHistoryDiagTypeAtBooking>
    </MAT104FamilyHistoryDiagnosisAtBooking>
    
    </MAT101BookingAppointmentDetails>
    
    
    -<MAT112DatingScanProcedure>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <ActivityOfferDate>2012-12-01</ActivityOfferDate>
    <OfferStatusDatingUltrasound>02</OfferStatusDatingUltrasound>
    <ProcedureDateDatingUltrasound>2013-01-04</ProcedureDateDatingUltrasound>
    <GestationDatingUltrasound>1</GestationDatingUltrasound>
    <NoFetusesDatingUltrasound>0</NoFetusesDatingUltrasound>
    <AbnormalityDatingUltrasound>N</AbnormalityDatingUltrasound>
    </MAT112DatingScanProcedure>
    
    
    -<MAT201BloodGroupRhesusTest>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <BloodSampleDateBloodGroupRhesusStatus>1900-01-01</BloodSampleDateBloodGroupRhesusStatus>
    <BloodGroup>77</BloodGroup>
    <RhesusGroup>777</RhesusGroup>
    <InvestigationResultRhesusAntibodies>2</InvestigationResultRhesusAntibodies>
    </MAT201BloodGroupRhesusTest>
    
    
    -<MAT203RubellaSusceptibilityTest>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <ActivityOfferDate>2013-01-02</ActivityOfferDate>
    <OfferStatusRubellaSusceptibility>01</OfferStatusRubellaSusceptibility>
    <BloodSampleDateRubellaSusceptibility>1900-01-01</BloodSampleDateRubellaSusceptibility>
    <InvestigationResultRubellaSusceptibility>03</InvestigationResultRubellaSusceptibility>
    </MAT203RubellaSusceptibilityTest>
    
    
    -<MAT205HepatitisBScreeningTest>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <ActivityOfferDate>2013-01-02</ActivityOfferDate>
    <OfferStatusHepatitisB>03</OfferStatusHepatitisB>
    <BloodSampleDateHepatitisB>1900-01-01</BloodSampleDateHepatitisB>
    <InvestigationResultHepatitisB>01</InvestigationResultHepatitisB>
    </MAT205HepatitisBScreeningTest>
    
    
    -<MAT210AsymptomaticBacteriuriaScreeningOffer>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <ActivityOfferDate>2013-01-02</ActivityOfferDate>
    <OfferStatusAsymptomaticBacteriuria>04</OfferStatusAsymptomaticBacteriuria>
    </MAT210AsymptomaticBacteriuriaScreeningOffer>
    
    
    -<MAT211HaemoglobinopathyScreeningTest>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <ActivityOfferDate>2013-01-02</ActivityOfferDate>
    <OfferStatusHaemoglobinopathy>03</OfferStatusHaemoglobinopathy>
    <BloodSampleDateHaemoglobinopathy>2013-01-10</BloodSampleDateHaemoglobinopathy>
    <InvestigationResultHaemoglobinopathy>04</InvestigationResultHaemoglobinopathy>
    </MAT211HaemoglobinopathyScreeningTest>
    
    
    -<MAT301MaternityCarePlan>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <CarePlanDate>2012-12-05</CarePlanDate>
    <CarePlanType>05</CarePlanType>
    <LeadProfessionalType>060</LeadProfessionalType>
    <SiteCodeIntendedDelivery>5BC</SiteCodeIntendedDelivery>
    <PlaceTypeIntendedDelivery>0</PlaceTypeIntendedDelivery>
    <PlaceTypeIntendedMidwifery>1</PlaceTypeIntendedMidwifery>
    <DeliveryPlaceChangeReasonCode>2</DeliveryPlaceChangeReasonCode>
    </MAT301MaternityCarePlan>
    
    
    -<MAT303DownsSymdromeScreeningTest>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <ActivityOfferDate>2013-01-02</ActivityOfferDate>
    <OfferStatusDownsSyndrome>03</OfferStatusDownsSyndrome>
    <BloodTestSampleDateDownsSyndrome>2013-01-10</BloodTestSampleDateDownsSyndrome>
    <InvestivationRiskRatioDownsSyndrome>0.0</InvestivationRiskRatioDownsSyndrome>
    <MaternityTestBookletGivenDate>1900-01-01</MaternityTestBookletGivenDate>
    </MAT303DownsSymdromeScreeningTest>
    
    
    -<MAT305FetalAnomalyScreeningTest>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <FetalOrderUltrasoundAnomaly>1</FetalOrderUltrasoundAnomaly>
    <ActivityOfferDate>2013-01-02</ActivityOfferDate>
    <OfferStatusUltrasoundFetalAnomaly>02</OfferStatusUltrasoundFetalAnomaly>
    <ProcedureDateTimeFetalAnomaly>2013-01-30T13:00:00</ProcedureDateTimeFetalAnomaly>
    <InvestigationResultUltrasoundFetalAnomaly>03</InvestigationResultUltrasoundFetalAnomaly>
    </MAT305FetalAnomalyScreeningTest>
    
    
    -<MAT306AntenatalAppointment>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <AntenatalAppDate>2013-03-31</AntenatalAppDate>
    </MAT306AntenatalAppointment>
    
    
    -<MAT307MedicalDiag>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <DiagnosisDate>2013-01-02</DiagnosisDate>
    <PregnancyMedicalDiagType>21</PregnancyMedicalDiagType>
    </MAT307MedicalDiag>
    
    
    -<MAT309MaternityObstetricDiag>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <DiagnosisDate>2013-01-02</DiagnosisDate>
    <PregnancyObstetricDiagType>08</PregnancyObstetricDiagType>
    </MAT309MaternityObstetricDiag>
    
    
    -<MAT310AntenatalAdmission>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <HPSAntenatalStartDate>2013-01-01</HPSAntenatalStartDate>
    <HPSAntenatalDischargeDate>2013-01-02</HPSAntenatalDischargeDate>
    </MAT310AntenatalAdmission>
    
    
    -<MAT404LabourAndDelivery>
    <LabourOnsetDateTime>2013-05-15T13:00:00</LabourOnsetDateTime>
    <CaesareanDateTime>2013-05-15T13:00:00</CaesareanDateTime>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <LabourOnsetPresentation>01</LabourOnsetPresentation>
    <StartDateTimeMotherDeliveryHPS>2013-05-15T12:00:00</StartDateTimeMotherDeliveryHPS>
    <DecisionToDeliverDateTime>2013-05-15T16:00:00</DecisionToDeliverDateTime>
    <ROMDateTime>2013-05-15T16:00:00</ROMDateTime>
    <ROMMethod>NA</ROMMethod>
    <ROMReason>03</ROMReason>
    <LabourOnsetSecondStageDateTime>2013-05-15T16:00:00</LabourOnsetSecondStageDateTime>
    <LabourThirdStageEndDateTime>2013-05-15T16:00:00</LabourThirdStageEndDateTime>
    <EpisiotomyReason>02</EpisiotomyReason>
    <PlacentaDeliveryMethod>03</PlacentaDeliveryMethod>
    <DischargeDateTimeMotherDeliveryHPS>2013-05-15T16:00:00</DischargeDateTimeMotherDeliveryHPS>
    <OrgCodePostnatalPathLeadProvider>6TY</OrgCodePostnatalPathLeadProvider>
    
    
    -<MAT401MedicalInductionMethod>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <LabourOnsetDateTime>2013-05-15T13:00:00</LabourOnsetDateTime>
    <LabourInductionMethod>05</LabourInductionMethod>
    <OxytocinAdministeredDateTime>2013-05-15T13:00:00</OxytocinAdministeredDateTime>
    </MAT401MedicalInductionMethod>
    
    
    -<MAT405LabourDeliveryPainRelief>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <LabourOnsetDateTime>2013-05-15T13:00:00</LabourOnsetDateTime>
    <CaesareanDateTime>2013-05-15T13:00:00</CaesareanDateTime>
    <LabourPainReliefMethod>02</LabourPainReliefMethod>
    </MAT405LabourDeliveryPainRelief>
    
    
    -<MAT406LabourDeliveryAnaesthesia>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <LabourOnsetDateTime>2013-05-15T13:00:00</LabourOnsetDateTime>
    <CaesareanDateTime>2013-05-15T13:00:00</CaesareanDateTime>
    <LabourAnaesthesiaType>01</LabourAnaesthesiaType>
    </MAT406LabourDeliveryAnaesthesia>
    
    
    -<MAT409GenitalTractTrauma>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <LabourOnsetDateTime>2013-05-15T13:00:00</LabourOnsetDateTime>
    <GenitalTractTraumaticLesion>09</GenitalTractTraumaticLesion>
    </MAT409GenitalTractTrauma>
    
    </MAT404LabourAndDelivery>
    
    
    -<MAT408MCI>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <MCIDateTime>2013-05-15T16:00:00</MCIDateTime>
    <MCIType>06</MCIType>
    </MAT408MCI>
    
    
    -<MAT501FetusOutcome>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <FetusOutcomeOrder>0</FetusOutcomeOrder>
    <FetusOutcomeDate>2013-05-15</FetusOutcomeDate>
    <FetusOutcome>40</FetusOutcome>
    </MAT501FetusOutcome>
    
    
    -<MAT502BabysDemographicsAndBirthDetails>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <OrgCodeLocalPatientIdBaby>5BC</OrgCodeLocalPatientIdBaby>
    <BabyBirthDateTime>2013-05-15T16:15:00</BabyBirthDateTime>
    <NHSNumberBaby>1111111112</NHSNumberBaby>
    <NHSNumberStatusBaby>01</NHSNumberStatusBaby>
    <PersonPhenotypicSex>1</PersonPhenotypicSex>
    <PersonDeathDateTimeBaby>1900-01-01T00:00:00</PersonDeathDateTimeBaby>
    <BirthOrderMaternitySUS>1</BirthOrderMaternitySUS>
    <BirthWeight>0150</BirthWeight>
    <GestationLengthBirth>270</GestationLengthBirth>
    <DeliveryMethodBaby>0</DeliveryMethodBaby>
    <WaterDeliveryInd>Y</WaterDeliveryInd>
    <ApgarScore5>0</ApgarScore5>
    <SiteCodeActualDelivery>5BC</SiteCodeActualDelivery>
    <PlaceTypeActualDelivery>6</PlaceTypeActualDelivery>
    <PlaceTypeActualMidwifery>3</PlaceTypeActualMidwifery>
    <BabyFirstFeedDateTime>2013-05-15T16:30:00</BabyFirstFeedDateTime>
    <BabyFirstFeedBreastMilkStatus>02</BabyFirstFeedBreastMilkStatus>
    <BabyBreastMilkStatusDischarge>01</BabyBreastMilkStatusDischarge>
    <SkinToSkinContact1Hour>Y</SkinToSkinContact1Hour>
    
    
    -<MAT504BabyComplicationsAtBirth>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <BabyComplicationAtBirth>02</BabyComplicationAtBirth>
    </MAT504BabyComplicationsAtBirth>
    
    
    -<MAT506NeonatalResuscitationMethod>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <NeonatalResuscitationMethod>04</NeonatalResuscitationMethod>
    </MAT506NeonatalResuscitationMethod>
    
    
    -<MAT507NeonatalResuscitationDrugFluid>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <NeonatalResuscitationDrugFluid>03</NeonatalResuscitationDrugFluid>
    </MAT507NeonatalResuscitationDrugFluid>
    
    
    -<MAT508NeonatalCriticalCareAdmission>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <NeonatalTransferStartDateTime>2013-05-15T16:15:00</NeonatalTransferStartDateTime>
    <SiteCodeAdmittingNeonatal>5BC</SiteCodeAdmittingNeonatal>
    </MAT508NeonatalCriticalCareAdmission>
    
    
    -<MAT510NeonatalDiag>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <DiagnosisDate>2013-05-15</DiagnosisDate>
    <NeonatalDiag>02</NeonatalDiag>
    </MAT510NeonatalDiag>
    
    
    -<MAT511NeonatalCriticalIncident>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <NeonatalCriticalIncidentDate>2013-05-15</NeonatalCriticalIncidentDate>
    <NeonatalCriticalIncidentType>05</NeonatalCriticalIncidentType>
    </MAT511NeonatalCriticalIncident>
    
    
    -<MAT513NewbornPhysicalScreening>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <ActivityOfferDate>2013-05-16</ActivityOfferDate>
    <NewbornPhysicalScreeningOfferStatus>03</NewbornPhysicalScreeningOfferStatus>
    <NewbornPhysicalScreeningDate>2013-05-16</NewbornPhysicalScreeningDate>
    <NewbornPhysicalExamResultHips>02</NewbornPhysicalExamResultHips>
    <NewbornPhysicalExamResultHeart>01</NewbornPhysicalExamResultHeart>
    <NewbornPhysicalExamResultEyes>01</NewbornPhysicalExamResultEyes>
    <NewbornPhysicalExamResultTestes>03</NewbornPhysicalExamResultTestes>
    </MAT513NewbornPhysicalScreening>
    
    
    -<MAT515NewbornHearingScreening>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <ActivityOfferDate>2013-05-16</ActivityOfferDate>
    <NewbornHearingScreeningOfferStatus>03</NewbornHearingScreeningOfferStatus>
    <NewbornHearingScreeningDate>2013-05-16</NewbornHearingScreeningDate>
    <NewbornHearingScreeningOutcome>16</NewbornHearingScreeningOutcome>
    </MAT515NewbornHearingScreening>
    
    
    -<MAT517NewbornBloodSpotScreening>
    <LocalPatientIdBaby>1112455788</LocalPatientIdBaby>
    <ActivityOfferDate>2013-05-16</ActivityOfferDate>
    <BloodSpotCardCompletionDate>2013-05-16</BloodSpotCardCompletionDate>
    <BloodSpotLabId>3ER</BloodSpotLabId>
    <PhenylketonuriaOfferStatus>NR</PhenylketonuriaOfferStatus>
    <PhenylketonuriaStatus>02</PhenylketonuriaStatus>
    <SickleCellOfferStatus>IE</SickleCellOfferStatus>
    <SickleCellStatus>02</SickleCellStatus>
    <CysticFibrosisOfferStatus>02</CysticFibrosisOfferStatus>
    <CysticFibrosisStatus>02</CysticFibrosisStatus>
    <CongenitalHypothyroidismOfferStatus>04</CongenitalHypothyroidismOfferStatus>
    <CongenitalHypothyroidismStatus>03</CongenitalHypothyroidismStatus>
    <MCACDDOfferStatus>03</MCACDDOfferStatus>
    <MCACDDStatus>02</MCACDDStatus>
    </MAT517NewbornBloodSpotScreening>
    
    </MAT502BabysDemographicsAndBirthDetails>
    
    
    -<MAT602PostpartumDischarge>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <MaternityDischargeDate>2013-05-15</MaternityDischargeDate>
    <EoPSmokingStatus>9</EoPSmokingStatus>
    </MAT602PostpartumDischarge>
    
    
    -<MAT603PostpartumReadmission>
    <LocalPatientIdMother>112552254</LocalPatientIdMother>
    <PostpartumHSPStartDateTime>2013-05-19T13:00:00</PostpartumHSPStartDateTime>
    <PostpartumHSPDischargeDateTime>2013-05-19T13:00:00</PostpartumHSPDischargeDateTime>
    </MAT603PostpartumReadmission>
    
    </MAT001MothersDemographics>
    
    
    -<MAT901ComplicatingSTIAtBooking>
    <AntenatalAppDate>2013-03-12</AntenatalAppDate>
    <ComplicatingSTIDiagAtBooking>11</ComplicatingSTIDiagAtBooking>
    </MAT901ComplicatingSTIAtBooking>
    
    
    -<MAT903SyphilisScreeningMother>
    <ActivityOfferDate>2013-03-12</ActivityOfferDate>
    <SyphilisScreeningOfferStatus>02</SyphilisScreeningOfferStatus>
    <SyphilisScreeningBloodSampleDate>2013-03-12</SyphilisScreeningBloodSampleDate>
    <SyphilisScreeningResult>03</SyphilisScreeningResult>
    </MAT903SyphilisScreeningMother>
    
    
    -<MAT905HIVScreeningMother>
    <ActivityOfferDate>2013-03-12</ActivityOfferDate>
    <HIVScreeningOfferStatus>04</HIVScreeningOfferStatus>
    <HIVScreeningBloodSampleDate>2013-03-12</HIVScreeningBloodSampleDate>
    <HIVScreeningResult>03</HIVScreeningResult>
    </MAT905HIVScreeningMother>
    
    
    -<MAT906MaternitySTIDiag>
    <DiagDate>2013-03-12</DiagDate>
    <MaternitySTIDiag>06</MaternitySTIDiag>
    </MAT906MaternitySTIDiag>
    </MATHDRHeader>
    </MSDS:MSDS>

    Friday, April 21, 2017 2:25 PM

All replies

  • The tables have not been created as yet but this is what I have been asked. We are in the process of building the main tables in our DW which these specifically designed tables/views will hang off with the intent to create an automated process to output into an XML file (which is mandatory for national submission).

    I have a valid schema (very long though) if required.

    Friday, April 21, 2017 2:30 PM
  • Hi SimonKEvans,

    It definitely should be possible.

    Please take a look at the following SQL Server statement:

    SELECT *
    FROM tbl
    FOR XML PATH('whatevertheneed')

    Also, if it is a recurring process, you can leverage use of SSIS and XSLT transformation.

    Friday, April 21, 2017 2:44 PM
  • WITH XMLNAMESPACES ('http://www.datadictionary.nhs.uk/messages/MSDS-v1-0' as MSDS)
    SELECT 1.0 AS Version
    , '5BC' as OrgCodeProv
    -- More here
    (
    select 112552254 as LocalPatientIdMother
    from SourceTable 
    FOR XML PATH ('MAT001MothersDemographics'), TYPE)
    -- Repeat for each
    
    FOR XML PATH ('MATHDRHeader'), ROOT ('MSDS:MSDS')

    You could do this with for XML Path statement.  You will need an outer query and a series of inner queries.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, April 21, 2017 3:41 PM