如何使用Access VBA导出具有各种记录标题的XML？ [英] How do I use Access VBA to export to XML with headings for various records?

问题描述

我正在使用Application.ExportXML编写代码，该代码会将各种Access查询导出到单个XML文件。到目前为止，我已经有了代码，并且可以正常工作，但是我想在要导出的查询数据中标记每个记录。例如，我在XML文件中列出了一个名为 portOfCallList的查询，但是我希望每个记录都被标记为 portOfCall。现在，一条记录如下所示：

I am writing a code using Application.ExportXML that will export various Access queries to a single XML file. I have the code so far and it is working, but I want to label each record within the query's data that will be exported. For example, I have one query listed in the XML file called "portOfCallList" but I want each record to be labeled "portOfCall". Right now one record looks like this:

- <portOfCallList>
    <arrivalDate>2015-07-17T00:00:00</arrivalDate> 
    <departureDate>2015-07-17T00:00:00</departureDate> 
    <portOfCallName>Southampton</portOfCallName> 
    <portOfCallCode>GBSOU</portOfCallCode> 
  </portOfCallList>

我希望它看起来像这样：

I want it to look like this:

- <portOfCallList>
  - <portOfCall>
      <arrivalDate>2015-07-17T00:00:00</arrivalDate> 
      <departureDate>2015-07-17T00:00:00</departureDate> 
      <portOfCallName>Southampton</portOfCallName> 
      <portOfCallCode>GBSOU</portOfCallCode> 
    </portOfCall>
    'And then have various other records also labeled "portOfCall" before ending with
  </portOfCallList>.

这是我唯一需要添加到代码中的东西，因为其余的代码都可以我需要做。

This is the only thing I need to add into my code, since the rest of the code does what I need it to do. I can post the code if need be.

让我知道是否可以进一步解释什么，谢谢！

Let me know if I can explain anything further, and thanks!

Kirby

推荐答案

要设置xml文件的样式，您需要使用 XSLT样式表。 XSL是用于转换xml文档的专用声明性语言。

To style your xml file you will need to use an XSLT stylesheet. XSL is a special-purpose declarative language used to transform xml documents.

因此，鉴于MS Access的输出，然后您可以使用MS Access VBA将原始输出xml转换为所需的已修改xml格式：

Hence, given your output from MS Access, you can then use MS Access VBA to transform the raw output xml to your required modified xml format:

首先，将以下内容另存为.xsl文件：

First, save the following as an .xsl file:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="UTF-8"/>

<xsl:template match="/">
  <xsl:element name="portOfCallList"><xsl:text>&#xA;</xsl:text>

    <xsl:for-each select="//portOfCallList">      
      <xsl:element name="portOfCall"><xsl:text>&#xA;</xsl:text>
        <xsl:copy-of select="arrivalDate"/><xsl:text>&#xA;</xsl:text>
        <xsl:copy-of select="departureDate"/><xsl:text>&#xA;</xsl:text>
        <xsl:copy-of select="portOfCallName"/><xsl:text>&#xA;</xsl:text>
        <xsl:copy-of select="portOfCallCode"/><xsl:text>&#xA;</xsl:text>
      </xsl:element><xsl:text>&#xA;</xsl:text>    
    </xsl:for-each>

  </xsl:element><xsl:text>&#xA;</xsl:text>
</xsl:template>

</xsl:stylesheet>

第二，运行VBA：

Public Sub PortOfCallXML()
    Dim xmlfile As Object, xslfile As Object, newxmlfile As Object
    Dim xmlstr As String, xslstr As String, newxmlstr As String

    Set xmlfile = CreateObject("MSXML2.DOMDocument")
    Set xslfile = CreateObject("MSXML2.DOMDocument")
    Set newxmlfile = CreateObject("MSXML2.DOMDocument")

    xmlstr = "C:\Path\To\RawXMLFile.xml"     ' ORIGINAL OUTPUT
    xslstr = "C:\Path\To\XSLFile.xsl"        ' FROM ABOVE SCRIPT
    newxmlstr = "C:\Path\To\NewXMLFile.xml"  ' NEW TRANSFORMED FILE

    xmlfile.async = False
    xmlfile.Load xmlstr

    xslfile.async = False
    xslfile.Load xslstr
    xmlfile.transformNodeToObject xslfile, newxmlfile
    newxmlfile.Save newxmlstr

    Set xmlfile = Nothing
    Set xslfile = Nothing
    Set newxmlfile = Nothing

    MsgBox "XML File successfully transformed!", vbInformation, "XML Transform Successful"
End Sub

输出看起来像预期的一样（我以您的示例重复了您发布的数据）：

Output would look as intended (I repeated your posted data as example):

<?xml version="1.0" encoding="UTF-8"?>
<portOfCallList>
    <portOfCall>
        <arrivalDate>2015-07-17T00:00:00</arrivalDate>
        <departureDate>2015-07-17T00:00:00</departureDate>
        <portOfCallName>Southampton</portOfCallName>
        <portOfCallCode>GBSOU</portOfCallCode>
    </portOfCall>
    <portOfCall>
        <arrivalDate>2015-07-17T00:00:00</arrivalDate>
        <departureDate>2015-07-17T00:00:00</departureDate>
        <portOfCallName>Southampton</portOfCallName>
        <portOfCallCode>GBSOU</portOfCallCode>
    </portOfCall>
    ...
</portOfCallList>

这篇关于如何使用Access VBA导出具有各种记录标题的XML？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！