0

My understanding is poor so really need a place to start looking for information.

I have 4 SQL tables of data. I would like to output all of them to an XML file.

I have an XML Schema to work with but no idea how to use it or where it should be used.

Long term objective is to have 30 hand off tables which we can run validation scripts against and then using the XML Scheme given generate 1 XML file for submission.

I am a novice here and learning as I go so any suggestions where to look would be appreciated.

SAMPLE XML DATA

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

</MAT101BookingAppointmentDetails>

</MATHDRHeader>

</MSDS:MSDS>

SAMPLE XDS

 <xs:complexType name="MSDSMAT001MothersDemographicsType">
      <xs:sequence>
         <xs:element name="LocalPatientIdMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>LOCAL PATIENT IDENTIFIER (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="OrgCodeLocalPatientIdMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>ORGANISATION CODE (LOCAL PATIENT IDENTIFIER (MOTHER))</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="OrgCodeRes" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>ORGANISATION CODE (RESIDENCE RESPONSIBILITY)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="NHSNumberMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>NHS NUMBER (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="NHSNumberStatusMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>NHS NUMBER STATUS INDICATOR CODE (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="PersonBirthDateMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>PERSON BIRTH DATE (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="Postcode" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>POSTCODE OF USUAL ADDRESS (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="EthnicCategoryMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>ETHNIC CATEGORY (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="PersonDeathDateTimeMother" type="ST" minOccurs="0" maxOccurs="1">
            <xs:annotation>
               <xs:appinfo>PERSON DEATH DATE TIME (MOTHER)</xs:appinfo>
            </xs:annotation>
         </xs:element>
         <xs:element name="MAT003GPPracticeRegistration" type="MSDSMAT003GPPracticeRegistrationType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT101BookingAppointmentDetails"
                     type="MSDSMAT101BookingAppointmentDetailsType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT112DatingScanProcedure" type="MSDSMAT112DatingScanProcedureType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT201BloodGroupRhesusTest" type="MSDSMAT201BloodGroupRhesusTestType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT203RubellaSusceptibilityTest"
                     type="MSDSMAT203RubellaSusceptibilityTestType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT205HepatitisBScreeningTest"
                     type="MSDSMAT205HepatitisBScreeningTestType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT210AsymptomaticBacteriuriaScreeningOffer"
                     type="MSDSMAT210AsymptomaticBacteriuriaScreeningOfferType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT211HaemoglobinopathyScreeningTest"
                     type="MSDSMAT211HaemoglobinopathyScreeningTestType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT301MaternityCarePlan" type="MSDSMAT301MaternityCarePlanType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT303DownsSyndromeScreeningTest"
                     type="MSDSMAT303DownsSyndromeScreeningTestType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT305FetalAnomalyScreeningTest"
                     type="MSDSMAT305FetalAnomalyScreeningTestType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT306AntenatalAppointment" type="MSDSMAT306AntenatalAppointmentType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT307MedicalDiag" type="MSDSMAT307MedicalDiagType" minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT309MaternityObstetricDiag" type="MSDSMAT309MaternityObstetricDiagType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT310AntenatalAdmission" type="MSDSMAT310AntenatalAdmissionType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT404LabourAndDelivery" type="MSDSMAT404LabourAndDeliveryType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT408MCI" type="MSDSMAT408MCIType" minOccurs="0" maxOccurs="unbounded"/>
         <xs:element name="MAT501FetusOutcome" type="MSDSMAT501FetusOutcomeType" minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT502BabysDemographicsAndBirthDetails"
                     type="MSDSMAT502BabysDemographicsAndBirthDetailsType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT602PostpartumDischarge" type="MSDSMAT602PostpartumDischargeType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
         <xs:element name="MAT603PostpartumReadmission" type="MSDSMAT603PostpartumReadmissionType"
                     minOccurs="0"
                     maxOccurs="unbounded"/>
      </xs:sequence>
   </xs:complexType>
Improve this question
15
  • 1
    Please provide more details (schema, reduced sample, expected output) and it might come out, that T-SQL is the wrong language for this... Are there any other tools / languages available? Commented Apr 12, 2017 at 7:30
  • The schema is 77000 characters long? I'm told SSIS is the better approach. Commented Apr 12, 2017 at 10:04
  • With .Net you could - for example - create a DataSet from a valid schema and then try to load data into this schema... In SQL Server there is rather poor support of schema-data-validation Commented Apr 12, 2017 at 10:08
  • I'm afraid the data with originate in SQL tables, as I say the objective to output a result set into an XML file Commented Apr 12, 2017 at 10:12
  • What I understand: There are some database tables with data and you want to export them to XML (files). You are hoping for some kind of automatism when you use a schema to get this easy going... Correct so far? One XML per table or one big inlcude everything monster? Commented Apr 12, 2017 at 10:28

1 Answer 1

Reset to default
1

Appears this one is too hard as cant get a reply anywhere online on any site

Well, I cannot let this pass unchallenged :-D

too hard must be discussed... One problem is, that the schema, as well as the data you provide is not complete / invalid... In your attempts to shorten the provided sample data (which is great!) you created invalid sample data... I would have to invest a lot of time first to understand what you really want and then to set up a mock-up project.

And now we are in the very center of too hard: I do not have the time for this...

SQL Server doesn't support a built-in schema check or schema related export automatisms. Therefore I told you in my first comment, that T-SQL might be the wrong tool...

You did not answer the question Are there any other tools / languages available ...

This code is a very simple C# code. It will create the internal structure of the dataset out of a schema and then load the given XML into this DataSet. This will fail, when the XML is not compatible. That might be a schema check for you.

        var ds = new System.Data.DataSet("TestSchema");
        ds.ReadXmlSchema(@"C:\SomePath\MotherSchema.xml");
        ds.ReadXml(@"C:\SomePath\MotherData.xml");

//The above in a try-catch, wrapped in a function might be enough as schema checked
//The following code would read the data into a string to check the success (totally untested...)

        var sb = new System.Text.StringBuilder();
        foreach (var t in ds.Tables) {
            var tbl = t as System.Data.DataTable;
            foreach (var r in tbl.Rows) {
                var rw = r as System.Data.DataRow;
                foreach (var c in tbl.Columns) {
                    var cl = c as System.Data.DataColumn;
                    sb.AppendLine(string.Format("{0}.{1}: {2}",tbl.TableName,cl.ColumnName,rw[cl].ToString()));
                }
            }
        }
        var str = sb.ToString();

Finally (as pointed out in my comment If I understand this correctly, you do not need a schema at all) I'd create the XML using FOR XML PATH exactly the way you need it. That is roughly the same approach your complicated Access legacy code is doing: Just create a correct XML. Does the Access solution reflect any schema? Probably not... See what I mean?

UPDATE Just to give you an idea:

USE master;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
GO

--Some tiny tables with tiny data. More columns are just more of the same...

CREATE TABLE MAT001MothersDemographics(ID BIGINT, Code VARCHAR(100));
INSERT INTO MAT001MothersDemographics VALUES(1111111,'1A1')
                                           ,(222222,'2B2');

CREATE TABLE MAT003GPPracticeRegistration(ID BIGINT,MotherID BIGINT,StartDate Date);
INSERT INTO MAT003GPPracticeRegistration VALUES(1,111111,{d'2001-01-01'})
                                              ,(2,222222,{d'2002-02-02'});

--I create the innner XML without namespaces as they seem to appear in the outer most node only...

DECLARE @MotherID BIGINT=222222;
DECLARE @innerXML XML= --without namespaces
(
SELECT '1.0' AS [MVersion]
      ,'5BC' AS [OrgCodeProv]
      ,GETDATE() AS [FileCreationDateTime]
      ,(
        SELECT ID AS LocalPatientIdMother
              ,Code AS OrgCodeLocalPatientIdMother
        FROM MAT001MothersDemographics
        WHERE ID=@MotherID
        FOR XML PATH('MAT001MothersDemographics'),TYPE
       )
      ,(
        SELECT MotherID AS LocalPatientIdMother
              ,StartDate AS StartDateGMPRegistration
        FROM MAT003GPPracticeRegistration
        WHERE MotherID=@MotherID
        FOR XML PATH('MAT003GPPracticeRegistration'),TYPE
       )
FOR XML PATH('MATHDRHeader')
);

--This will wrap the previously created XML with the <MSDS:MSDS> node

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi 
                  ,'http://www.datadictionary.nhs.uk/messages/MSDS-v1-0' AS MSDS
                  ,'http://www.datadictionary.nhs.uk/messages/MSDS-v1-0 ../Schemas/MSDSMSDS_XMLSchema-v1-0.xsd' AS schemaLocation)
SELECT @innerXML
FOR XML PATH('MSDS:MSDS');

--Clear Up

GO
USE master;
GO
DROP DATABASE TestDB;
GO

The result for this tiny portion look pretty much the way you need it:

    <MSDS:MSDS xmlns:schemaLocation="http://www.datadictionary.nhs.uk/messages/MSDS-v1-0 ../Schemas/MSDSMSDS_XMLSchema-v1-0.xsd" xmlns:MSDS="http://www.datadictionary.nhs.uk/messages/MSDS-v1-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <MATHDRHeader>
        <MVersion>1.0</MVersion>
        <OrgCodeProv>5BC</OrgCodeProv>
        <FileCreationDateTime>2017-04-21T10:45:57.590</FileCreationDateTime>
        <MAT001MothersDemographics>
          <LocalPatientIdMother>222222</LocalPatientIdMother>
          <OrgCodeLocalPatientIdMother>2B2</OrgCodeLocalPatientIdMother>
        </MAT001MothersDemographics>
        <MAT003GPPracticeRegistration>
          <LocalPatientIdMother>222222</LocalPatientIdMother>
          <StartDateGMPRegistration>2002-02-02</StartDateGMPRegistration>
        </MAT003GPPracticeRegistration>
      </MATHDRHeader>
    </MSDS:MSDS>
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.