I have a Microsoft Access database (via Office 2010) which is being used to manage course data, and I need to regularly export this data to a particular XML structure.
Unfortunately, I have found that the structure that is produced via the initial XML export is not then replicated when the 'saved export' is later re-run. I have conducted numerous tests and tried to research this online, but have not found a solution. Hoping somebody can please help!
The part of the database that is relevant looks like this: (this has been simplified from the original database, but the same problem still occurs).
Database structure - Screenshot
Some contextual info: A 'Course' is the overarching training programme which may run on several occasions. An 'Occurrence' is a particular instance/running of a course that the student actually enrols onto. A 'Unit' is a particular taught section of that Occurrence (e.g. practicals, seminars). 'Units' can form part of multiple 'Occurrences', thus the requirement for the many-to-many bridge table 'OccurrencesUnits'. This structure has been carefully planned and we are not realistically able to change this (and hopefully shouldn't need to).
When I export to XML (External Data > XML File), I select to export data from all of these tables:
Export XML settings - screenshot
When the initial export is complete, the resulting XML looks like this:
<?xml version="1.0" encoding="UTF-8"?> <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2015-11-25T16:09:31"> <Courses> <CourseID>1</CourseID> <CourseTitle>Meat Science</CourseTitle> <Occurrences> <OccurrenceID>1</OccurrenceID> <OccurrenceTitle>Meat Science - 10 Credit Version</OccurrenceTitle> <CourseID>1</CourseID> <OccurrencesUnits> <OccurrencesUnitsID>1</OccurrencesUnitsID> <OccurrenceID>1</OccurrenceID> <UnitID>1</UnitID> </OccurrencesUnits> </Occurrences> <Occurrences> <OccurrenceID>2</OccurrenceID> <OccurrenceTitle>Meat Science - 20 Credit Version</OccurrenceTitle> <CourseID>1</CourseID> <OccurrencesUnits> <OccurrencesUnitsID>2</OccurrencesUnitsID> <OccurrenceID>2</OccurrenceID> <UnitID>1</UnitID> </OccurrencesUnits> <OccurrencesUnits> <OccurrencesUnitsID>3</OccurrencesUnitsID> <OccurrenceID>2</OccurrenceID> <UnitID>2</UnitID> </OccurrencesUnits> </Occurrences> </Courses> <Units> <UnitID>1</UnitID> <UnitTitle>Meat Safety</UnitTitle> </Units> <Units> <UnitID>2</UnitID> <UnitTitle>Meat Genetics</UnitTitle> </Units> </dataroot> This is exactly the right data structure we want to have, whereby 'OccurrencesUnits' is nested within 'Occurrences'. I then save the export steps to use later within MS Access.
Unfortunately the problem occurs whenever I re-use this export step (via External Data > Saved Exports). This results in an XML file which is structured differently:
<?xml version="1.0" encoding="UTF-8"?> <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2015-11-25T16:10:34"> <Courses> <CourseID>1</CourseID> <CourseTitle>Meat Science</CourseTitle> <Occurrences> <OccurrenceID>1</OccurrenceID> <OccurrenceTitle>Meat Science - 10 Credit Version</OccurrenceTitle> <CourseID>1</CourseID> </Occurrences> <Occurrences> <OccurrenceID>2</OccurrenceID> <OccurrenceTitle>Meat Science - 20 Credit Version</OccurrenceTitle> <CourseID>1</CourseID> </Occurrences> </Courses> <OccurrencesUnits> <OccurrencesUnitsID>1</OccurrencesUnitsID> <OccurrenceID>1</OccurrenceID> <UnitID>1</UnitID> </OccurrencesUnits> <OccurrencesUnits> <OccurrencesUnitsID>2</OccurrencesUnitsID> <OccurrenceID>2</OccurrenceID> <UnitID>1</UnitID> </OccurrencesUnits> <OccurrencesUnits> <OccurrencesUnitsID>3</OccurrencesUnitsID> <OccurrenceID>2</OccurrenceID> <UnitID>2</UnitID> </OccurrencesUnits> <Units> <UnitID>1</UnitID> <UnitTitle>Meat Safety</UnitTitle> </Units> <Units> <UnitID>2</UnitID> <UnitTitle>Meat Genetics</UnitTitle> </Units> </dataroot> In this version, you can see that 'OccurrencesUnits' no longer resides within 'Occurrences', and now sits complete outside of the 'Occurrences' and 'Courses' tag. This is causing us a lot of difficulty as we need to have it in the original structure.
I don't understand why a 'Saved Export' does not actually replicate the original export from which it was saved. Could this be a bug in MS Access, or am I doing something wrong? I have tried re-creating the database several times, and have also tried exporting with/without an XML Schema, but cannot find a solution.
Hoping somebody can please help - It would be very much appreciated! Many thanks.
No comments:
Post a Comment