在MS Access 2013中指定精确的XML导出结构 [英] Specify exact XML export structure in MS Access 2013

问题描述

我有一个MS Access数据库，其中包含定期导出为XML的课程数据，以提供网站的数据。

我需要导出的表是主要的课程表，加上其他几个直接相关和间接相关的表，如发生，发生次数，单位，位置，员工 ， SubjectAreas 等。

借助我以前的堆栈溢出问题（可能值得阅读一些背景信息）我能够设置所需的确切的XML结构，其简化如下：

 <课程> 
< CourseID> 1< / CourseID> 
<课程标题>肉类科学< / CourseTitle> 
（etc etc）
< Occurrences> 
< OccurrenceID> 1< / OccurrenceID> 
< OccurrenceTitle>肉类科学秋季2016< / OccurrenceTitle> 
< CourseID> 1< / CourseID> 
< OccurrencesUnits> 
< OccurrencesUnitsID> 1< / OccurrencesUnitsID> 
< OccurrenceID> 1< / OccurrenceID> 
< UnitID> 1< / UnitID> 
< / OccurrencesUnits> 
< / Occurrences> 
< /课程> 
 
<课程> 
 ...更多课程... 
< /课程> 
 
< Units> 
 ... 
< / Units> 
 
<位置> 
 ... 
< / Locations> 
 
<员工> 
 ... 
< / Staff> 
 
< SubjectAreas> 
 ... 
< / SubjectAreas> 
 
 etc etc 
  

所以有几个层次，$ code>发生然后发生次数表嵌套在每个课程条目中，但是所有其他相关表格需要列在下方（不是嵌套的）。

然而，最近从Access 2010升级到2013年，我发现我无法生成相同的结构。看来，Access 2013通过将默认情况下将任何直接相关的表嵌套到主表节点来考虑表关系。当通过XSL指定布局结构（根据上面链接的相关堆栈溢出问题），这导致从XML导出中省略了直接相关的表，与2010年不同，它们自动出现在课程。

我发现accessforums.net上的另一个人发现了类似的问题： http://www.accessforums.net/showthread.php?t=46933 他们的解决方法是删除Access中的表关系，这在公平的情况下会导致预期的XML输出，但对我的数据库来说可能是相当糟糕的！

对于信息，我的VBA导出代码访问如下所示：

  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 
 
设置otherTables = Application.CreateAdditionalData 
 otherTables.AddOccurrences
 otherTables.AddOccurrencesUnits
 otherTables.AddUnits
 otherTables.AddLocations
 otherTables.AddCourseSubcategories
 otherTables.AddCourseTags
 otherTables.Add合作伙伴
 otherTables.AddStaff
 otherTables.AddSubjectAreas
 
 Application.ExportXML acExportTable，课程，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
 
设置rawDoc = CreateObject（MSXML2.DOMDocument）
设置xslDoc = CreateObject（MSXML2.DOMDocument）
设置newDoc = CreateObject（MSXML2.DOMDocument）
 
 rawDoc.async = False 
 rawDoc.Load xmlstr 
 
 xslDoc.async = False 
 xslDoc.Load xslstr 
 
'转换为新XML'
 rawDoc.transformNodeToObject xslDoc，newDoc 
 
'保存新的XML文件'
 newDoc.SaveC：\path\to\course-catalog.xml
 
 MsgBox成功导出XML，vbInformation 
 
 End Sub 
  

和我的 structure.xsl 如下所示：

 < xsl：transform xmlns：xsl =http://www.w3.org / 1999 / XSL / Transformversion =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.0encoding =UTF-8indent =yes/> 
< xsl：strip-space elements =*/> 
 
<！ - 身份转换 - > 
< xsl：template match =@ * | node（）> 
< xsl：copy> 
< xsl：apply-templates select =@ * | node（）/> 
< / xsl：copy> 
< / xsl：template> 
 
< xsl：template match =课程> 
<课程> 
< xsl：copy-of select =CourseID/> 
< xsl：copy-of select =CourseTitle/> 
（etc etc）
< xsl：for-each select =Occurrences> 
<发生> 
< xsl：copy-of select =*/> 
< xsl：variable name =occidselect =occurrenceID/> 
< xsl：copy-of select =../../ OccurrencesUnits [CourseOccurrence = $ occid]/> 
< / Occurrences> 
< / xsl：for-each> 
< /课程> 
< / xsl：template> 
 
< xsl：template match =OccurrencesUnits/> 
 
< / xsl：transform> 
  

所以我的问题 - 是否可以指定（可能通过XSL）我需要的确切结构，即/ code>和发生次数不超过课程 ？非常感谢您的任何支持！

于2016年8月17日添加

要澄清，MS Access 2013生成的XML可以在下面看到。与2010年类似，但问题是与Courses表相关的任何与直接相关的表被省略（ CourseSubcategories ， CourseTags ，合作伙伴，员工和 SubjectAreas ），唯一的方法是将其关系删除到Access中的课程。包含单位和位置 的表格（因为它们不直接 c code code code code
< CourseID> 1< / CourseID>
<课程标题>肉类科学< / CourseTitle>
（etc etc）
< Occurrences>
< OccurrenceID> 1< / OccurrenceID>
< OccurrenceTitle>肉类科学秋季2016< / OccurrenceTitle>
< CourseID> 1< / CourseID>
< OccurrencesUnits>
< OccurrencesUnitsID> 1< / OccurrencesUnitsID>
< OccurrenceID> 1< / OccurrenceID>
< UnitID> 1< / UnitID>
< / OccurrencesUnits>
< / Occurrences>
< /课程>

<课程>
...更多课程...
< /课程>

< Units>
...
< / Units>

<位置>
...
< / Locations>





解决方案

考虑导出相关表的各个temp xml文件不出现然后，使用 将Xml转换为XSLT， code> document（） 功能。 VBA将在转换后删除它们。这种方法的一个非常重要的设置是XSLT（.xsl）脚本必须与XML文件位于与其相关的文件引用相同的文件夹中：

VBA

  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 
 Dim temp As Variant 
 
设置otherTables =应用程序.CreateAdditionalData 
 otherTables.AddOccurrences
 otherTables.AddOccurrencesUnits
 
'EXPORT MAIN XML 
 Application.ExportXML acExportTable，Courses，S： \Science\Biosciences\AATP\Database\xml\course-catalog.xml，_ 
，，，，，，，AdditionalData：= otherTables 
 
'EXPORT TEMP XMLS 
对于Array中的每个temp（Units，Locations，CourseSubcategories，CourseTags，Partners，员工，SubjectAreas）
 Application.ExportXML acExportTable，temp，S：\Science\Biosciences\AATP\Database\xml\温度.xml
 Next temp 
 
'LOAD XML和XSL FILES'
 xmlstr =C：\path\to\course-catalog.xml
 xslstr =C：\path\to\structure.xsl
 
设置rawDoc = CreateObject（MSXML2.DOMDocument）
设置xslDoc = CreateObject（MSXML2 DOMDocument）
设置newDoc = CreateObject（MSXML2.DOMDocument）
 
 rawDoc.async = False 
 rawDoc.setPropertyAllowDocumentFunction，True 
 rawDoc.Load xmlstr 
 
 xslDoc.async = False 
 xslDoc.setPropertyAllowDocumentFunction，True 
 xslDoc.Load xslstr 
 
'TRANSFORM TO NEW XML'
 rawDoc.transformNodeToObject xslDoc，newDoc 
 
'保存新的XML文件'
 newDoc.SaveC：\path\to\course-catalog.xml
 
'DELETE TEMP XMLs 
对于Array中的每个temp（Units，Locations，CourseSubcategories，CourseTags，Partners，Staff，SubjectAreas）
 xmlfile =S：\Science\Biosc iences\AATP\Database\xml\&温度.xml
如果Len（Dir（xmlfile，vbDirectory））> 0然后杀死xmlfile 
下一个temp 
 
 MsgBox成功导出XML，vbInformation 
 
 End Sub 
  pre> 
 
 
  XSLT  （必须保存在与其他.xml文件相同的目录中） 
 < xsl：transform xmlns：xsl =http://www.w3.org/1999/XSL/Transformversion =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.0encoding =UTF-8indent =yes/> 
< xsl：strip-space elements =*/> 
 
< xsl：template match =/ dataroot> 
< xsl：copy> 
< xsl：apply-templates select =@ * | node（）/> 
< xsl：copy-of select =document（'CourseSubcategories.xml'）/ dataroot / CourseSubcategories/> 
< xsl：copy-of select =document（'CourseTags.xml'）/ dataroot / CourseTags/> 
< xsl：copy-of select =document（'Partners.xml'）/ dataroot / Partners/> 
< xsl：copy-of select =document（'Staff.xml'）/ dataroot / Staff/> 
< xsl：copy-of select =document（'SubjectAreas.xml'）/ dataroot / SubjectAreas/> 
< / xsl：copy> 
< / xsl：template> 
 
< xsl：template match =课程> 
<课程> 
< xsl：copy-of select =CourseID/> 
< xsl：copy-of select =CourseTitle/> 
（etc etc）
< xsl：for-each select =Occurrences> 
<发生> 
< xsl：copy-of select =*/> 
< xsl：variable name =occidselect =occurrenceID/> 
< xsl：copy-of select =ancestor :: dataroot / OccurrencesUnits [CourseOccurrence = $ occid]/> 
< / Occurrences> 
< / xsl：for-each> 
< /课程> 
< / xsl：template> 
 
< xsl：template match =OccurrencesUnits/> 
 
< / xsl：transform> 
  
 
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>

解决方案
Consider exporting individual temp xml files for those related tables that do not appear. Then, include those xmls in the XSLT transformation using the document() function. VBA will delete them after transformation. One very important setup in this approach is the XSLT (.xsl) script must reside in same folder as the XML files as it makes relative file references:

VBA
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
  Dim temp As Variant

  Set otherTables = Application.CreateAdditionalData
  otherTables.Add "Occurrences"
  otherTables.Add "OccurrencesUnits"

  ' EXPORT MAIN XML 
  Application.ExportXML acExportTable, "Courses", "S:\Science\Biosciences\AATP\Database\xml\course-catalog.xml", _
             , , , , , , AdditionalData:=otherTables

  ' EXPORT TEMP XMLS
  For Each temp in Array("Units", "Locations", "CourseSubcategories", "CourseTags", "Partners", "Staff", "SubjectAreas")
      Application.ExportXML acExportTable, temp, "S:\Science\Biosciences\AATP\Database\xml\" & temp & ".xml" 
  Next temp 

  ' 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.setProperty "AllowDocumentFunction", True
  rawDoc.Load xmlstr

  xslDoc.async = False
  xslDoc.setProperty "AllowDocumentFunction", True
  xslDoc.Load xslstr

  ' TRANSFORM TO NEW XML '
  rawDoc.transformNodeToObject xslDoc, newDoc

  ' SAVE NEW XML FILE '
  newDoc.Save "C:\path\to\course-catalog.xml"

  ' DELETE TEMP XMLs
  For Each temp in Array("Units", "Locations", "CourseSubcategories", "CourseTags", "Partners", "Staff", "SubjectAreas")
      xmlfile = "S:\Science\Biosciences\AATP\Database\xml\" & temp & ".xml"
      If Len(Dir(xmlfile, vbDirectory)) > 0 Then Kill xmlfile
  Next temp 

  MsgBox "Successfully exported XML.", vbInformation

End Sub
XSLT (must be saved in same directory as other .xml files)
<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="*"/>

<xsl:template match="/dataroot">
  <xsl:copy>
    <xsl:apply-templates select="@*|node()"/>
    <xsl:copy-of select="document('CourseSubcategories.xml')/dataroot/CourseSubcategories"/>
    <xsl:copy-of select="document('CourseTags.xml')/dataroot/CourseTags"/>
    <xsl:copy-of select="document('Partners.xml')/dataroot/Partners"/>
    <xsl:copy-of select="document('Staff.xml')/dataroot/Staff"/>
    <xsl:copy-of select="document('SubjectAreas.xml')/dataroot/SubjectAreas"/>
  </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="ancestor::dataroot/OccurrencesUnits[CourseOccurrence=$occid]"/>
      </Occurrences>
      </xsl:for-each>        
  </Courses>
</xsl:template>

<xsl:template match="OccurrencesUnits"/>

</xsl:transform>


                        
这篇关于在MS Access 2013中指定精确的XML导出结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！