Exporting Access Table into XML using VBA

Question

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

Parfait · Accepted Answer · 2016-06-28 03:01:17Z

2

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>

answered Jun 28, 2016 at 3:01

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

John Wolfenstein Over a year ago

I tested both and the first option worked great aside from the fact that there was no indentation, which I believe does not really affect an XML data table. The second option gave me an error saying "only one top level element is allowed in an XML document". Doesn't really matter except for my own knowledge as the first option works great.

Parfait Over a year ago

Great! Please accept if answer helped. Second option will indent properly. I cannot recreate that error. Be sure paths point to correct files.

John Wolfenstein Over a year ago

I am still trying to navigate the difference between answers and comments. I did add something to my original question regarding option 2 regarding there being something not coded correctly.

Parfait Over a year ago

Do you still get the error? Does your xml look exactly like your posted snippet? Post first elements. Did you properly copy the .xsl script? To be clear, XSL is an XML file and follows same rules with open/ending tags. Finally what version of MS Access do you run? What Windows version?

John Wolfenstein Over a year ago

I cut and pasted from your example and as far as I can tell they appear to be the same. Option 1 works fine, option 2 returns a run time error that says "Only one top level element is allowed in an XML document". I am running Access 2013 with Windows 10. I am not that concerned as I do have your solution. I am just trying to educate myself as much as I can with regard to option 2. I very much appreciate your help.

Collectives™ on Stack Overflow

Exporting Access Table into XML using VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related