How do I use Access VBA to export to XML with head

Question

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

Answer 1

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.

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:

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>

Second, run the 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>