How to import XML to Access single table

[gaby58]

Hi All,

I have this XML file and when I tried to import it will import to different tables, I would like to know how to import theses in to single table called StudentRecords, thank you for the information. I need this really badly


Also if I have more than 1 file like this, how to combine and add them into single Table?

Best Regards

[orange]

The xml contains info about several entities. To put this into 1 table would be contrary to a normalized structure.

Here is a quick attempt to show different entities (only first part of your file)

    <MDStudentRecord>
        <StudentIdentification>
            <StudentUniqueID>17965</StudentUniqueID>
            <Name>
                <FirstName>Mary</FirstName>
                <MiddleName>Ethan</MiddleName>
                <LastName>Garner</LastName>
            </Name>
            <StudentID>642621</StudentID>
            <PriorID>64269</PriorID>
            <LocalID>WARNELUC0</LocalID>
            <Address>
                <Address>218 OLD OAKS ST</Address>
                <City>LEAGUE CITY</City>
                <StateProvince>TX</StateProvince>
                <Country>US</Country>
                <PostalCode>77573</PostalCode>
            </Address>
        </StudentIdentification>
        <Parent>
            <Name>
                <FirstName>John</FirstName>
                <LastName>Warner</LastName>
            </Name>
        </Parent>
        <Parent>
            <Name>
                <FirstName>Kelly</FirstName>
                <MiddleName>Ann</MiddleName>
                <LastName>Warner</LastName>
            </Name>
        </Parent>
        <StudentDemographic>
            <Gender>M</Gender>
            <Ethnicity>
                <AmericanIndianAlaskaNativeCode>0</AmericanIndianAlaskaNativeCode>
                <AsianCode>0</AsianCode>
                <BlackAfricanAmericanCode>0</BlackAfricanAmericanCode>
                <HispanicLatinoCode>0</HispanicLatinoCode>
                <NativeHawaiianPacificIslanderCode>0</NativeHawaiianPacificIslanderCode>
                <WhiteCode>1</WhiteCode>
            </Ethnicity>
            <BirthDate>1998-08-12</BirthDate>
            <Language>98</Language>
            <Migrant>0</Migrant>
            <Homeless>0</Homeless>
            <Dyslexia>0</Dyslexia>
            <AsyleeRefugeeCode>0</AsyleeRefugeeCode>
        </StudentDemographic>
        <School>
            <SchoolID>10009</SchoolID>
            <SchoolName>CLEAR SPRINGS H S</SchoolName>
            <DistrictID>084910</DistrictID>
            <DistrictName>CLEAR CREEK ISD</DistrictName>
            <Address>
                <Address>501 PALOMINO LN</Address>
                <City>LEAGUE CITY</City>
                <StateProvince>TX</StateProvince>
                <Country>US</Country>
                <PostalCode>77573</PostalCode>
            </Address>
            <PhoneNumber>2812841300</PhoneNumber>
            <Fax>2812841306</Fax>
        </School>
        <Enrollment>
            <GradeLevel>11</GradeLevel>
            <EntryDate>2015-10-05</EntryDate>
            <ExitDate>2017-06-01</ExitDate>
        </Enrollment>
        <SpecialPrograms>
            <LEP>0</LEP>
            <EconomicDisadvantaged>01</EconomicDisadvantaged>
            <AtRisk>1</AtRisk>
            <TitleI>0</TitleI>
            <SpecialEd>0</SpecialEd>
            <IEP>0</IEP>
            <Bilingual>0</Bilingual>
            <ESL>0</ESL>
            <GiftedTalented>0</GiftedTalented>
            <CTE>1</CTE>
            <PersonalGradPlan>0</PersonalGradPlan>
        </SpecialPrograms>
        <Attendance>
            <DaysAttended>153.0</DaysAttended>
            <DaysAbsent>024.0</DaysAbsent>
        </Attendance>
        <Immunization>
            <ImmunizationCode>Diphtheria, Tetanus, whole cell Per</ImmunizationCode>
            <ImmunizationDate>1998-10-12</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Diphtheria, Tetanus, whole cell Per</ImmunizationCode>
            <ImmunizationDate>1998-12-29</ImmunizationDate>
            <ImmunizationDose>2</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Diphtheria, Tetanus, whole cell Per</ImmunizationCode>
            <ImmunizationDate>1999-03-12</ImmunizationDate>
            <ImmunizationDose>3</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Diphtheria, Tetanus, whole cell Per</ImmunizationCode>
            <ImmunizationDate>1999-07-28</ImmunizationDate>
            <ImmunizationDose>4</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Diphtheria, Tetanus, whole cell Per</ImmunizationCode>
            <ImmunizationDate>2003-07-28</ImmunizationDate>
            <ImmunizationDose>5</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Diphtheria, Tetanus, whole cell Per</ImmunizationCode>
            <ImmunizationDate>2011-08-19</ImmunizationDate>
            <ImmunizationDose>6</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Haemophilus Influenzae type b</ImmunizationCode>
            <ImmunizationDate>1998-10-12</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Haemophilus Influenzae type b</ImmunizationCode>
            <ImmunizationDate>1998-12-29</ImmunizationDate>
            <ImmunizationDose>2</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Haemophilus Influenzae type b</ImmunizationCode>
            <ImmunizationDate>1999-03-12</ImmunizationDate>
            <ImmunizationDose>3</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Haemophilus Influenzae type b</ImmunizationCode>
            <ImmunizationDate>1999-09-02</ImmunizationDate>
            <ImmunizationDose>4</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Hepatitis A</ImmunizationCode>
            <ImmunizationDate>2011-08-19</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Hepatitis B</ImmunizationCode>
            <ImmunizationDate>1998-08-12</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Hepatitis B</ImmunizationCode>
            <ImmunizationDate>1998-10-12</ImmunizationDate>
            <ImmunizationDose>2</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Hepatitis B</ImmunizationCode>
            <ImmunizationDate>1999-03-12</ImmunizationDate>
            <ImmunizationDose>3</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Inactivated Polio Vaccine</ImmunizationCode>
            <ImmunizationDate>1998-10-12</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Inactivated Polio Vaccine</ImmunizationCode>
            <ImmunizationDate>1998-12-28</ImmunizationDate>
            <ImmunizationDose>2</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Inactivated Polio Vaccine</ImmunizationCode>
            <ImmunizationDate>1999-03-12</ImmunizationDate>
            <ImmunizationDose>3</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Inactivated Polio Vaccine</ImmunizationCode>
            <ImmunizationDate>1999-07-25</ImmunizationDate>
            <ImmunizationDose>4</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Inactivated Polio Vaccine</ImmunizationCode>
            <ImmunizationDate>2003-07-28</ImmunizationDate>
            <ImmunizationDose>5</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Measles, Mumps, Rubella</ImmunizationCode>
            <ImmunizationDate>1999-07-28</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Measles, Mumps, Rubella</ImmunizationCode>
            <ImmunizationDate>1999-09-02</ImmunizationDate>
            <ImmunizationDose>2</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Measles, Mumps, Rubella</ImmunizationCode>
            <ImmunizationDate>2003-07-28</ImmunizationDate>
            <ImmunizationDose>3</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Meningococcal conjugate</ImmunizationCode>
            <ImmunizationDate>2011-08-19</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Varicella</ImmunizationCode>
            <ImmunizationDate>2003-07-28</ImmunizationDate>
            <ImmunizationDose>1</ImmunizationDose>
        </Immunization>
        <Immunization>
            <ImmunizationCode>Varicella</ImmunizationCode>
            <ImmunizationDate>2011-08-19</ImmunizationDate>
            <ImmunizationDose>2</ImmunizationDose>
        </Immunization>
        <AcademicStatus>
            <GraduationDiplomaType>34</GraduationDiplomaType>
            <FHSPParticipationCode>1</FHSPParticipationCode>
            <FHSPDistingIndicatorCode>0</FHSPDistingIndicatorCode>
            <STEMEndorsementCode>0</STEMEndorsementCode>
            <BusinessEndorsementCode>0</BusinessEndorsementCode>
            <PublicServicesEndorsementCode>0</PublicServicesEndorsementCode>
            <ArtsEndorsementCode>0</ArtsEndorsementCode>
            <MultiStudiesEndorsementCode>0</MultiStudiesEndorsementCode>
        </AcademicStatus>
        <CurrentCoursework>
            <Course>
                <CourseTitle>ENG 2: COMP &amp; CRITICAL RDG</CourseTitle>
                <CourseID>03220200</CourseID>
                <Abbreviation>ENG 2</Abbreviation>
                <Semester>1</Semester>
                <SubjectArea>LC</SubjectArea>
                <CourseCredit>0.0</CourseCredit>
                <Explanation>L</Explanation>
                <Explanation>T</Explanation>
                <CreditCampus>084910009</CreditCampus>
                <SemesterSeq>0</SemesterSeq>
            </Course>
            <Course>
                <CourseTitle>ENG 2: COMP &amp; CRITICAL RDG</CourseTitle>
                <CourseID>03220200</CourseID>
                <Abbreviation>ENG 2</Abbreviation>
                <Semester>2</Semester>
                <SubjectArea>LC</SubjectArea>
                <GradeAverage>70</GradeAverage>
                <CourseCredit>0.5</CourseCredit>
                <Explanation>L</Explanation>
                <Explanation>T</Explanation>
                <CreditCampus>084910009</CreditCampus>
                <SemesterSeq>0</SemesterSeq>
            </Course>
            <Course>
                <CourseTitle>ADVISORY 11</CourseTitle>
                <CourseID>85000ADV</CourseID>
                <Abbreviation>ADV</Abbreviation>
                <Semester>1</Semester>
                <ClassPeriod>0</ClassPeriod>
                <SubjectArea>LC</SubjectArea>
                <CourseCredit>0.0</CourseCredit>
                <CourseTeacher>Karpowicz Boring, S</CourseTeacher>
                <CreditCampus>084910009</CreditCampus>
                <SemesterSeq>0</SemesterSeq>
            </Course>
            <Course>
                <CourseTitle>ADVISORY 11</CourseTitle>
                <CourseID>85000ADV</CourseID>
                <Abbreviation>ADV</Abbreviation>
                <Semester>2</Semester>
                <ClassPeriod>0</ClassPeriod>
                <SubjectArea>LC</SubjectArea>
                <CourseCredit>0.0</CourseCredit>
                <CourseTeacher>Karpowicz Boring, S</CourseTeacher>
                <CreditCampus>084910009</CreditCampus>
                <SemesterSeq>0</SemesterSeq>
            </Course>
            <Course>
                <CourseTitle>SOCIOLOGY</CourseTitle>
                <CourseID>03370100</CourseID>
                <Abbreviation>SOC</Abbreviation>
                <Semester>1</Semester>
                <ClassPeriod>1</ClassPeriod>
                <SubjectArea>SS</SubjectArea>
                <CourseGrade>67 - P1</CourseGrade>
                <CourseGrade>80 - P2</CourseGrade>
                <CourseGrade>79 - Q1</CourseGrade>
                <CourseGrade>100 - P3</.......

I am attaching a jpg of how Access 2010 would import your Test.xml data.
One table per recognized entity.

[gaby58]

So you mean to say, it is not possible with this XML structure to load them into single table?

[orange]

Not possible is a loosely defined term. (At what cost; for what purpose; for which user(s)....)
Why would you want to put these into 1 table in a relational database system?
Do you have any experience with relational databases?
Are you aware/familiar with Normalization?

What do you plan to do with this data once you get it into Access?

[gaby58]

This data is needed to import all the grade and course information of student to another system, so if we have all the info in one table, it will be easy to build the import. I understand that the way it is importing data, it is not possible. I know how the normalization works, it is the Primary Key and Foreign key relationship. That's why I want to know how to put all these table data into one main table with Student ID as the primary key. Thank you for the information

[orange]

What is the other system you have to supply data to?
I think your approach is not appropriate. I would suggest you bring the xml into the tables suggested by Access. Then, depending on your specifications for data in the other system, create the proper query(s) to select the data you need. If the second system supports ODBC and you have access to it, you might consider updating/appending directly to those tables from the Access tables created via importing the test.xml.

If you are familiar with xml and xlst, you might manipulate the xml to give only the data you need without involving all of the tables proposd by the Access importer. But that depends on the details of your needs.

The approach to take really depends on your needs and skills. But putting this organized data into a non organized single table does not seem to be a viable strategy to me. Others may have a different view.

Good luck with your project.