MS对XML“保存的导出”的访问在重新导出时会生成不同的XML结构 [英] MS Access to XML 'Saved Export' produces different XML structure when re-exported

问题描述

我有一个用于管理课程数据的Microsoft Access数据库（通过Office 2010），我需要定期将此数据导出到特定的XML结构。

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.

不幸的是，我发现通过初始XML导出生成的结构在以后重新运行保存的导出时不会被复制。我进行了大量的测试，并试图在网上研究，但还没有找到一个解决方案。希望有人可以请帮助！

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).

某些上下文信息：A'课程是可以多次运行的总体培训计划。 出现是学生实际进入的课程的特定实例/运行。 单位是该事件中特定的教导部分（例如实践，研讨会）。 单位可以形成多个出现次数的一部分，因此需要多对多桥表OccurrencesUnits。

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).

当我导出到XML（外部数据> XML）时，文件），我选择导出所有这些表中的数据：

When I export to XML (External Data > XML File), I select to export data from all of these tables:

初始导出完成后，生成的XML如下所示：

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>

这是我们想要的正确的数据结构，因此'OccurrencesUnits'嵌套在'Occurrences '。然后保存导出步骤以在以后在MS Access中使用。

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.

不幸的是，当我重新使用此导出步骤（通过外部数据>保存的导出）时出现问题。这会产生一个结构不同的XML文件：

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>

在此版本中，您可以看到OccurrencesUnits不再驻留在Occurrences坐在Occurrences和Courses标签之外。这使我们有很多困难，因为我们需要在原始结构中。

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.

我不明白为什么保存的导出实际上不复制它的原始出口，它被保存。这可能是一个错误在MS Access，或者我做错了吗？我尝试重新创建数据库几次，并尝试导出/不使用XML模式，但是找不到解决方案。

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.

推荐答案

我无法在MS Access中重新创建您的问题。我通过手动XML导出向导和保存导出，并产生相同的结果，即您的最后一个格式，其中前两个相关表（课程和出现）嵌套在最后一个表（OccurrenceUnits）堆叠在末尾。我可以说的是，您保存的导出是依赖于表之间已建立的关系，而不仅仅是导出表或内部连接SQL查询表。

I can't quite re-create your issue in MS Access. I went through the manual XML Export wizard and Saved Export and both produce the same result, namely your last format where first two related tables (Courses and Occurrences) are nested with last table (OccurrenceUnits) stacked at the end. What I can say is your saved export is contingent on established relationships between tables and not simply an export of table or inner join SQL query of tables.

但是，解决方案，具体自动化您的确切的结构需求。您需要的内容包括 XSLT （声明性编程语言，可重新格式化，重新格式化，结构XML文档）。 VBA可以使用 MSXML XSLT脚本转换XML。 / a>对象。您可以继续运行已保存的导出（使用表关系），然后加载外部XML和XSLT文件进行转换。

However, consider a programming solution that specifically automates your exact structural needs. And what you need involves XSLT (the declarative programming language that re-styles, re-formats, re-structures XML documents). VBA can transform XML with XSLT scripts using the MSXML object. You can continue to run your saved export (using table relationships) and then load the external XML and XSLT files for the transformation.

XSLT em>（保存为外部.xsl或.xslt以在下面使用）

XSLT (save as external .xsl or .xslt to be used below)

<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"/>
        <xsl:for-each select="Occurences">
            <xsl:copy-of select="*"/>
            <xsl:variable name="occid" select="OccurenceID"/>
            <xsl:copy-of select="../../OccurrencesUnits[OccurenceID=$occid]"/>
        </xsl:for-each>        
    </Courses>
  </xsl:template>

  <xsl:template match="OccurrencesUnits"/>

</xsl:transform>

VBA 子例程

以下是XML导出的两个选项：1）使用保存的导出对象或2）自动的 Application.ExportXML ，您可以在其中将其他相关表格添加到Courses输出：

Below are two options of XML export: 1) using your Saved Export object or 2) the automated Application.ExportXML where you add other related tables to Courses output:

Private Sub XMLOutput_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"        

    ' RUN SAVED XML EXPORT ' 
    'DoCmd.RunSavedImportExport "SavedXMLExportName"

    ' RUN AUTOMATED EXPORT XML WITH NESTED ADDITIONAL TABLES ' 
    Application.ExportXML acExportTable, "Courses", "C:\Path\To\Original\XMLfile.xml", _
                 , , , , , , AdditionalData:=otherTables

    ' LOAD XML AND XSL FILES '
    xmlstr = "C:\Path\To\Original\XMLfile.xml"
    xslstr = "C:\Path\To\XSLT\Script.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\Output\XMLfile.xml"

    MsgBox "Successfully transformed xml!", vbInformation

End Sub

这篇关于MS对XML“保存的导出”的访问在重新导出时会生成不同的XML结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！