Import XML with multiple nested rows into Access

Question

I'm importing some XML documents (created in InfoPath) into MS Access using an XSLT. It's working fine except for one issue. Many of the nested elements (child tables) contain multiple values, but the import is only bringing in one record.

In this sample, there are 3 UnsafeProcedures, but only the first one gets imported to the tblUnsafeProcedures table.

XML

<my:myFields
    <my:DateOfIncident>2017-01-10T16:00:00</my:DateOfIncident>
    <my:IncidentID>20170125T1452</my:IncidentID>
    <my:IncidentLocation>Mayberry</my:IncidentLocation>
    <my:InvolvedPerson>Joe Blow</my:InvolvedPerson>
    <my:tblUnsafeProcedures>
        <my:IncidentID>20170125T1452</my:IncidentID>
        <my:UnsafeProcedures>Other (explain)</my:UnsafeProcedures>
        <my:UnsafeProcedures>Unsafe Lifting</my:UnsafeProcedures>
        <my:UnsafeProcedures>Using equipment unsafely</my:UnsafeProcedures>
    </my:tblUnsafeProcedures>
</my:myFields>

Here is the VBA that runs the import

    Public Sub TransformAndImportMultipleXMLs()
    On Error GoTo Err_Trap
    Dim strFile As String, strPath As String
    Dim xmlDoc As New MSXML2.DOMDocument60
    Dim xslDoc As New MSXML2.DOMDocument60
    Dim newDoc As New MSXML2.DOMDocument60strPath = "y:\"
    strFile = Dir(strPath & "*.xml")

    xslDoc.async = False
    xslDoc.validateOnParse = False
    xslDoc.resolveExternals = False

    xslDoc.Load "C:\JSTAdb\IncidentID.xslt"

    While strFile <> ""
        ' REINITIALIZE DOM OBJECTS
        Set xmlDoc = New MSXML2.DOMDocument60
        Set newDoc = New MSXML2.DOMDocument60

        ' LOAD XML SOURCE
        xmlDoc.Load strPath & strFile

        ' TRANSFORM SOURCE
        xmlDoc.transformNodeToObject xslDoc, newDoc
        newDoc.Save "C:\JSTAdb\temp.xml"

        ' APPEND TO TABLES
        Application.ImportXML "C:\JSTAdb\temp.xml", acAppendData
        Call WaitFor(1) 'Wait for 1 second between imports
        strFile = Dir()
    Wend

   ' RELEASE DOM OBJECTS
    Set xmlDoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing
Err_Trap:
    Debug.Print Error
    Resume Next
End Sub

There must be a way to import all of these child elements. What am I missing?

XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2016-02-16T15:55:31"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"
xml:lang="en-US">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="my:grpUnsafeWorkProcedures">
        <my:tblUnsafeProcedures>
            <my:IncidentID><xsl:value-of select="../my:IncidentID"/></my:IncidentID>
            <xsl:apply-templates select="@*|node()"/>
        </my:tblUnsafeProcedures>
    </xsl:template>

</xsl:stylesheet>

Below is the original XML file before transformation. This is an InfoPath form. The UnsafeProcedures field is a multi-selection list box with 7 items to choose from. In this case, 3 items were chosen.

Original XML

<?xml version="1.0" encoding="utf-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:INCIDENT-INVESTIGATION-REPORTS:-myXSD-2016-02-16T15-55-31" solutionVersion="1.0.0.1597" productVersion="15.0.0.0" PIVersion="1.0.0.0" href="http://sharepoint.acme.com/sites/safety/RecAndNonRec/Forms/template.xsn"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.4"?>
<?mso-infoPath-file-attachment-present?>
<my:myFields my:Why4="Joe messed up" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:pc="http://schemas.microsoft.com/office/infopath/2007/PartnerControls" xmlns:ma="http://schemas.microsoft.com/office/2009/metadata/properties/metaAttributes" xmlns:d="http://schemas.microsoft.com/office/infopath/2009/WSSList/dataFields" xmlns:q="http://schemas.microsoft.com/office/infopath/2009/WSSList/queryFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:dms="http://schemas.microsoft.com/office/2009/documentManagement/types" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2016-02-16T15:55:31" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
    <my:DateOfIncident>2017-01-10T16:00:00</my:DateOfIncident>
    <my:IncidentLocation>Maberry</my:IncidentLocation>
    <my:InvolvedPerson>Joe Blow</my:InvolvedPerson>
    <my:grpUnsafeWorkProcedures>
        <my:UnsafeProcedures></my:UnsafeProcedures><my:UnsafeProcedures>Other (explain)</my:UnsafeProcedures><my:UnsafeProcedures>Unsafe Lifting</my:UnsafeProcedures><my:UnsafeProcedures>Using equipment unsafely</my:UnsafeProcedures>
    </my:grpUnsafeWorkProcedures>
    <my:IncidentID>20170125T1452</my:IncidentID>
</my:myFields>

You can just use the DOM to get the contents of individual elements, and parse them manually. See [How to parse XML using VBA](https://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba) — Erik A, Oct 15 '17 at 18:53
Of course only one does, column must be unique. Please post XSLT which needs to modify for each *UnsafeProcedures*. — Parfait, Oct 16 '17 at 14:28
It looks like your XML sample is the result of your current transformation (creating `my:tblUnsafeProcedures`). Are you now looking for a second XSLT file to further transform your data? If not, then an example of the *original* XML (including the root node) might be helpful. — Gord Thompson, Oct 16 '17 at 16:18

Gord Thompson · Accepted Answer · 2017-10-16T20:47:09.207

Based on a fleshed-out version of the XML sample in your original question

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2016-02-16T15:55:31">
    <my:myFields>
        <my:DateOfIncident>2017-01-10T16:00:00</my:DateOfIncident>
        <my:IncidentID>20170125T1452</my:IncidentID>
        <my:IncidentLocation>Mayberry</my:IncidentLocation>
        <my:InvolvedPerson>Joe Blow</my:InvolvedPerson>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20170125T1452</my:IncidentID>
            <my:UnsafeProcedures>Other (explain)</my:UnsafeProcedures>
            <my:UnsafeProcedures>Unsafe Lifting</my:UnsafeProcedures>
            <my:UnsafeProcedures>Using equipment unsafely</my:UnsafeProcedures>
        </my:tblUnsafeProcedures>
    </my:myFields>
    <my:myFields>
        <my:DateOfIncident>2001-02-03T04:05:06</my:DateOfIncident>
        <my:IncidentID>20010123T1234</my:IncidentID>
        <my:IncidentLocation>Space</my:IncidentLocation>
        <my:InvolvedPerson>HAL 9000</my:InvolvedPerson>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20010123T1234</my:IncidentID>
            <my:UnsafeProcedures>Killed crew member</my:UnsafeProcedures>
            <my:UnsafeProcedures>Refused to open pod bay doors</my:UnsafeProcedures>
        </my:tblUnsafeProcedures>
    </my:myFields>
</dataroot>

the XSLT file

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2016-02-16T15:55:31" 
    xml:lang="en-US">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="my:tblUnsafeProcedures">
    <xsl:apply-templates select="@*|node()"/>
  </xsl:template>

  <xsl:template match="my:UnsafeProcedures">
    <my:tblUnsafeProcedures>
      <my:IncidentID>
        <xsl:value-of select="../my:IncidentID"/>
      </my:IncidentID>
      <my:Procedure>
        <xsl:value-of select="node()"/>
      </my:Procedure>
    </my:tblUnsafeProcedures>
  </xsl:template>

</xsl:stylesheet>

produces the following XML output

<?xml version="1.0" encoding="UTF-16"?>
<dataroot xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2016-02-16T15:55:31">
    <my:myFields>
        <my:DateOfIncident>2017-01-10T16:00:00</my:DateOfIncident>
        <my:IncidentID>20170125T1452</my:IncidentID>
        <my:IncidentLocation>Mayberry</my:IncidentLocation>
        <my:InvolvedPerson>Joe Blow</my:InvolvedPerson>
        <my:IncidentID>20170125T1452</my:IncidentID>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20170125T1452</my:IncidentID>
            <my:Procedure>Other (explain)</my:Procedure>
        </my:tblUnsafeProcedures>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20170125T1452</my:IncidentID>
            <my:Procedure>Unsafe Lifting</my:Procedure>
        </my:tblUnsafeProcedures>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20170125T1452</my:IncidentID>
            <my:Procedure>Using equipment unsafely</my:Procedure>
        </my:tblUnsafeProcedures>
    </my:myFields>
    <my:myFields>
        <my:DateOfIncident>2001-02-03T04:05:06</my:DateOfIncident>
        <my:IncidentID>20010123T1234</my:IncidentID>
        <my:IncidentLocation>Space</my:IncidentLocation>
        <my:InvolvedPerson>HAL 9000</my:InvolvedPerson>
        <my:IncidentID>20010123T1234</my:IncidentID>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20010123T1234</my:IncidentID>
            <my:Procedure>Killed crew member</my:Procedure>
        </my:tblUnsafeProcedures>
        <my:tblUnsafeProcedures>
            <my:IncidentID>20010123T1234</my:IncidentID>
            <my:Procedure>Refused to open pod bay doors</my:Procedure>
        </my:tblUnsafeProcedures>
    </my:myFields>
</dataroot>

which when imported into Access results in

[myFields]

DateOfIncident       IncidentID     IncidentLocation  InvolvedPerson
-------------------  -------------  ----------------  --------------
2017-01-10T16:00:00  20170125T1452  Mayberry          Joe Blow      
2001-02-03T04:05:06  20010123T1234  Space             HAL 9000      

[tblUnsafeProcedures]

IncidentID     Procedure                    
-------------  -----------------------------
20170125T1452  Other (explain)              
20170125T1452  Unsafe Lifting               
20170125T1452  Using equipment unsafely     
20010123T1234  Killed crew member           
20010123T1234  Refused to open pod bay doors

I see now. I need to Transform my original XML, then Transform it again as you show. It works! Many thanks! — Jeff Shall, Oct 16 '17 at 20:31
You don't need to transform it again. Only one XSLT is needed. Replace your version. — Parfait, Oct 17 '17 at 17:08

Import XML with multiple nested rows into Access

1 Answers1