title: Import XML with multiple nested rows into Access
body: <p>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.</p>
  
  <p>In this sample, there are 3 UnsafeProcedures, but only the first one gets imported to the tblUnsafeProcedures table.</p>
  
  <p><strong>XML</strong></p>
  
  <pre><code>&lt;my:myFields
      &lt;my:DateOfIncident&gt;2017-01-10T16:00:00&lt;/my:DateOfIncident&gt;
      &lt;my:IncidentID&gt;20170125T1452&lt;/my:IncidentID&gt;
      &lt;my:IncidentLocation&gt;Mayberry&lt;/my:IncidentLocation&gt;
      &lt;my:InvolvedPerson&gt;Joe Blow&lt;/my:InvolvedPerson&gt;
      &lt;my:tblUnsafeProcedures&gt;
          &lt;my:IncidentID&gt;20170125T1452&lt;/my:IncidentID&gt;
          &lt;my:UnsafeProcedures&gt;Other (explain)&lt;/my:UnsafeProcedures&gt;
          &lt;my:UnsafeProcedures&gt;Unsafe Lifting&lt;/my:UnsafeProcedures&gt;
          &lt;my:UnsafeProcedures&gt;Using equipment unsafely&lt;/my:UnsafeProcedures&gt;
      &lt;/my:tblUnsafeProcedures&gt;
  &lt;/my:myFields&gt;
  </code></pre>
  
  <p>Here is the <strong>VBA</strong> that runs the import</p>
  
  <pre><code>    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 &amp; "*.xml")
  
      xslDoc.async = False
      xslDoc.validateOnParse = False
      xslDoc.resolveExternals = False
  
      xslDoc.Load "C:\JSTAdb\IncidentID.xslt"
  
      While strFile &lt;&gt; ""
          ' REINITIALIZE DOM OBJECTS
          Set xmlDoc = New MSXML2.DOMDocument60
          Set newDoc = New MSXML2.DOMDocument60
  
          ' LOAD XML SOURCE
          xmlDoc.Load strPath &amp; 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
  </code></pre>
  
  <p>There must be a way to import all of these child elements. What am I missing?</p>
  
  <p><strong>XSLT</strong></p>
  
  <pre><code>&lt;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"&gt;
      &lt;xsl:output indent="yes"/&gt;
      &lt;xsl:strip-space elements="*"/&gt;
  
      &lt;xsl:template match="@*|node()"&gt;
          &lt;xsl:copy&gt;
              &lt;xsl:apply-templates select="@*|node()"/&gt;
          &lt;/xsl:copy&gt;
      &lt;/xsl:template&gt;
  
      &lt;xsl:template match="my:grpUnsafeWorkProcedures"&gt;
          &lt;my:tblUnsafeProcedures&gt;
              &lt;my:IncidentID&gt;&lt;xsl:value-of select="../my:IncidentID"/&gt;&lt;/my:IncidentID&gt;
              &lt;xsl:apply-templates select="@*|node()"/&gt;
          &lt;/my:tblUnsafeProcedures&gt;
      &lt;/xsl:template&gt;
  
  &lt;/xsl:stylesheet&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Original XML</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;?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"?&gt;
  &lt;?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.4"?&gt;
  &lt;?mso-infoPath-file-attachment-present?&gt;
  &lt;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"&gt;
      &lt;my:DateOfIncident&gt;2017-01-10T16:00:00&lt;/my:DateOfIncident&gt;
      &lt;my:IncidentLocation&gt;Maberry&lt;/my:IncidentLocation&gt;
      &lt;my:InvolvedPerson&gt;Joe Blow&lt;/my:InvolvedPerson&gt;
      &lt;my:grpUnsafeWorkProcedures&gt;
          &lt;my:UnsafeProcedures&gt;&lt;/my:UnsafeProcedures&gt;&lt;my:UnsafeProcedures&gt;Other (explain)&lt;/my:UnsafeProcedures&gt;&lt;my:UnsafeProcedures&gt;Unsafe Lifting&lt;/my:UnsafeProcedures&gt;&lt;my:UnsafeProcedures&gt;Using equipment unsafely&lt;/my:UnsafeProcedures&gt;
      &lt;/my:grpUnsafeWorkProcedures&gt;
      &lt;my:IncidentID&gt;20170125T1452&lt;/my:IncidentID&gt;
  &lt;/my:myFields&gt;
  </code></pre>