CODEWITHSUNDEEP
xmlvbams-access
John Wolfenstein
John Wolfenstein

Reputation: 141

Exporting Access Table into XML using VBA

I have an Access Table that was originally imported from XML. When I export it I do not have the same XML structure as the original XML table.

The original imported table looks as follows:

<?xml version="1.0" standalone="true"?>
<profiling>
  <program>
    <name>118CDSpro</name>
    <p1on>1</p1on>
    <p1tool>36</p1tool>
    <p2on>OFF</p2on>
    <cut>OFF</cut>
    <rule>OFF</rule>
    <desc>118 Clad DirectSet Profile</desc>
    <pic>akv.bmp</pic>
    <ten>dilec_F</ten>
  </program>
<profiling/>

This is what I get in an export:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot generated="2016-06-27T12:16:29" xmlns:od="urn:schemas microsoft-com:officedata">
  <Profiling>
     <name>Din_C92S_pro2</name>
     <p1on>1</p1on>
     <p1tool>40</p1tool>
     <p2on>OFF</p2on>
     <cut>OFF</cut>
     <rule>OFF</rule>
     <desc>Inswing Door Sash Profile 2 (Storm)</desc>
     <ten>dilec_F</ten>
  </Profiling>
</dataroot>

I think the problem lies within an .xsl file, but I am new to VBA and am unsure of how it is used.

This code has to very specific as to the original format.

Any suggestions would be very appreciated.

Option 2 Code:

Sub ProfileXML2()

    ' RAW XML EXPORT
    Application.ExportXML acExportTable, "Profiling", "C:\MyData\Crafter 0610\Crafter\MACHINE\SCHEMAS\ProfileExport.xml"


    ' TRANSFORM RAW XML (OPTION 2 - full XSLT processor)
    Dim xmlDoc As Object, xslDoc As Object, newDoc As Object

    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Set xslDoc = CreateObject("MSXML2.DOMDocument")
    Set newDoc = CreateObject("MSXML2.DOMDocument")

    ' LOAD XML AND XSL FILES
    xmlDoc.Load "C:\MyData\Crafter 0610\Crafter\MACHINE\SCHEMAS\ProfileExport.xml"
    xmlDoc.async = False

    xslDoc.Load "C:\MyData\Crafter 0610\Crafter\MACHINE\SCHEMAS\ProfilingSchema.xsl"
    xslDoc.async = False

    ' TRANSFORM SOURCE TO FINAL
    xmlDoc.transformNodeToObject xslDoc, newDoc
    newDoc.Save "C:\MyData\Crafter 0610\Crafter\DATA\ProfilingTest.xml"

    Set newDoc = Nothing
    Set xslDoc = Nothing
    Set xmlDoc = Nothing

End Sub

Upvotes: 2

Views: 4846

Answers (1)

Parfait
Parfait

Reputation: 107652

Currently, there is no problem. MS Access does not retain the imported XML file structure. The output you receive is the standard template of tables or query output in XML format. But since your end use needs cannot accommodate this raw output, consider using XSLT, the special-purpose language designed to transform XML documents.

You can either run the XSLT with Application.TransformXML or with the MSXML library. Below VBA code shows both options. This XSLT is a special script as the first two template matches remove the namespace, urn:schemas microsoft-com:officedata, from output returning local element names.

XSLT script (save as .xsl to be loaded in VBA)

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

  <xsl:template match="@*|node()">
    <xsl:element name="{local-name()}">
      <xsl:apply-templates select="@*|node()"/>
    </xsl:element>
  </xsl:template>

  <xsl:template match="text()">
    <xsl:copy/>
  </xsl:template>

  <xsl:template match="dataroot">    
    <xsl:apply-templates select="Profiling"/>    
  </xsl:template>

  <xsl:template match="Profiling">
    <profiling>
      <program>
        <xsl:apply-templates select="*"/>
      </program>
    </profiling>
  </xsl:template>

</xsl:stylesheet>

VBA script

Public Sub XMLHandle()

    ' RAW XML EXPORT
    Application.ExportXML acExportTable, "TableName", "C:\Path\To\Raw\Output.xml"

    ' TRANSFORM RAW XML (OPTION 1 - limited XSLT method)    
    Application.TransformXML "C:\Path\To\Raw\Output.xml", _
                             "C:\Path\To\XSLT\Transform.xsl", _
                             "C:\Path\To\Final\Output.xml"

    ' TRANSFORM RAW XML (OPTION 2 - full XSLT processor)
    Dim xmlDoc As Object, xslDoc As Object, newDoc As Object

    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Set xslDoc = CreateObject("MSXML2.DOMDocument")
    Set newDoc = CreateObject("MSXML2.DOMDocument")

    ' LOAD XML AND XSL FILES
    xmlDoc.Load "C:\Path\To\Raw\Output.xml"
    xmlDoc.async = False

    xslDoc.Load "C:\Path\To\XSLT\Transform.xsl"
    xslDoc.async = False

    ' TRANSFORM SOURCE TO FINAL
    xmlDoc.transformNodeToObject xslDoc, newDoc
    newDoc.Save "C:\Path\To\Final\Output.xml"

    Set newDoc = Nothing
    Set xslDoc = Nothing
    Set xmlDoc = Nothing

End Sub

Output

<?xml version="1.0" encoding="UTF-8"?>
<profiling>
    <program>
        <name>Din_C92S_pro2</name>
        <p1on>1</p1on>
        <p1tool>40</p1tool>
        <p2on>OFF</p2on>
        <cut>OFF</cut>
        <rule>OFF</rule>
        <desc>Inswing Door Sash Profile 2 (Storm)</desc>
        <ten>dilec_F</ten>
    </program>
</profiling>

Upvotes: 2

Related Questions