I have an MS Access database containing course data which is regularly exported to XML to provide data for a website.
The tables I need to export are a primary Courses table, plus several other directly related and indirectly related tables such as Occurrences, OccurrencesUnits, Units, Locations, Staff, SubjectAreas, etc.
With the help of my previous Stack Overflow question (may be worth reading for some background info) I was able to set up the exact XML structure needed, which is simplified below:
<Courses>
<CourseID>1</CourseID>
<CourseTitle>Meat Science</CourseTitle>
(etc etc)
<Occurrences>
<OccurrenceID>1</OccurrenceID>
<OccurrenceTitle>Meat Science Autumn 2016</OccurrenceTitle>
<CourseID>1</CourseID>
<OccurrencesUnits>
<OccurrencesUnitsID>1</OccurrencesUnitsID>
<OccurrenceID>1</OccurrenceID>
<UnitID>1</UnitID>
</OccurrencesUnits>
</Occurrences>
</Courses>
<Courses>
... more courses...
</Courses>
<Units>
...
</Units>
<Locations>
...
</Locations>
<Staff>
...
</Staff>
<SubjectAreas>
...
</SubjectAreas>
etc etc
So there are a few tiers whereby the Occurrences and then OccurrencesUnits tables are nested inside each of their Courses entries, but then all the other related tables need to be listed underneath (not nested).
However, on recently upgrading from Access 2010 to 2013 I have found that I cannot produce the same structure. It seems that Access 2013 takes into account table relationships by nesting any directly related tables into the primary table nodes by default. When specifying the layout structure via XSL (as per my related Stack Overflow question, linked above), this leads to the directly related tables being omitted from the XML export, unlike in 2010 where they automatically appeared underneath the Courses.
I found that another person on accessforums.net has identified a similar problem: http://www.accessforums.net/showthread.php?t=46933 Their solution was to delete the table relationships in Access, which in fairness does lead to the intended XML output but would presumably be rather bad for my database!
For info, my VBA export code in Access looks like this:
Private Sub ExportCourseCatalogXML_Click()
Dim rawDoc As Object, xslDoc As Object, newDoc As Object
Dim xmlstr As String, xslstr As String
Dim otherTables As AdditionalData
Set otherTables = Application.CreateAdditionalData
otherTables.Add "Occurrences"
otherTables.Add "OccurrencesUnits"
otherTables.Add "Units"
otherTables.Add "Locations"
otherTables.Add "CourseSubcategories"
otherTables.Add "CourseTags"
otherTables.Add "Partners"
otherTables.Add "Staff"
otherTables.Add "SubjectAreas"
Application.ExportXML acExportTable, "Courses", "S:\Science\Biosciences\AATP\Database\xml\course-catalog.xml", _
, , , , , , AdditionalData:=otherTables
' LOAD XML AND XSL FILES '
xmlstr = "C:\path\to\course-catalog.xml"
xslstr = "C:\path\to\structure.xsl"
Set rawDoc = CreateObject("MSXML2.DOMDocument")
Set xslDoc = CreateObject("MSXML2.DOMDocument")
Set newDoc = CreateObject("MSXML2.DOMDocument")
rawDoc.async = False
rawDoc.Load xmlstr
xslDoc.async = False
xslDoc.Load xslstr
' TRANSFORM TO NEW XML '
rawDoc.transformNodeToObject xslDoc, newDoc
' SAVE NEW XML FILE '
newDoc.Save "C:\path\to\course-catalog.xml"
MsgBox "Successfully exported XML.", vbInformation
End Sub
and my structure.xsl looks like this:
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
xmlns:od="urn:schemas-microsoft-com:officedata"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
exclude-result-prefixes="od xsi">
<xsl:output version="1.0" encoding="UTF-8" indent="yes" />
<xsl:strip-space elements="*"/>
<!-- Identity Transform -->
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="Courses">
<Courses>
<xsl:copy-of select="CourseID"/>
<xsl:copy-of select="CourseTitle"/>
(etc etc)
<xsl:for-each select="Occurrences">
<Occurrences>
<xsl:copy-of select="*"/>
<xsl:variable name="occid" select="occurrenceID"/>
<xsl:copy-of select="../../OccurrencesUnits[CourseOccurrence=$occid]"/>
</Occurrences>
</xsl:for-each>
</Courses>
</xsl:template>
<xsl:template match="OccurrencesUnits"/>
</xsl:transform>
So my question - Is it possible to specify (presumably via XSL) the exact structure I require, i.e. all tables listed outside of Courses except for Occurrences and OccurrencesUnits? Many thanks in advance for any support on this!
Added 17th Aug 2016:
For clarification, the XML that MS Access 2013 produces can be seen below. It is similar to 2010, but the problem is that any tables that are directly related to the Courses table are omitted (CourseSubcategories, CourseTags, Partners, Staff and SubjectAreas), and the only way to include them is to delete their relationships to Courses in Access. The tables of Units and Locations are included (because they are not directly related to Courses).
<Courses>
<CourseID>1</CourseID>
<CourseTitle>Meat Science</CourseTitle>
(etc etc)
<Occurrences>
<OccurrenceID>1</OccurrenceID>
<OccurrenceTitle>Meat Science Autumn 2016</OccurrenceTitle>
<CourseID>1</CourseID>
<OccurrencesUnits>
<OccurrencesUnitsID>1</OccurrencesUnitsID>
<OccurrenceID>1</OccurrenceID>
<UnitID>1</UnitID>
</OccurrencesUnits>
</Occurrences>
</Courses>
<Courses>
... more courses...
</Courses>
<Units>
...
</Units>
<Locations>
...
</Locations>
