title: Flatten an XML with xslt then Import into Access
body: <p>I've been trying for 3 weeks to use an XML file in Access. I've tried a couple of tactics such as converting to CSV and having a linked table but favour flattening the XML then importing it with VBA.
  I've seen lots of helpful examples and have learned a lot, but none of them let me complete the job. I'm a bit of a novice.
  It's important I understand as I need to take on a bigger XML file next, so I'll be adapting the XSLT to suit the bigger file next, inspired by your help.</p>
  
  <p>A snippet of the XML is here:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;STOREITEMS&gt;&lt;PRODUCT ITEM="R7876" NAME="Gloves Pair"&gt;
          &lt;STOCK&gt;No Stock.&lt;/STOCK&gt;&lt;/PRODUCT&gt;
      &lt;PRODUCT ITEM="BR122293" NAME="Silver Duo Balls"&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;&lt;/PRODUCT&gt;
      &lt;PRODUCT ITEM="cloneboyb" NAME="Cast Your Own Kit"&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;&lt;/PRODUCT&gt;
      &lt;PRODUCT ITEM="ASTRO" NAME="Astroglide"&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;&lt;/PRODUCT&gt;
      &lt;PRODUCT ITEM="3002028110" NAME="Translucence Pink"&gt;
      &lt;STOCK&gt;In Stock&lt;/STOCK&gt;&lt;/PRODUCT&gt;
      &lt;PRODUCT ITEM="0340" NAME="Cream 82g Lubricant"&gt;
          &lt;STOCK&gt;No Stock.&lt;/STOCK&gt;&lt;/PRODUCT&gt;
  &lt;/STOREITEMS&gt;
  </code></pre>
  
  <p>To be compatible with Access I need:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;STOCK&gt;
      &lt;PRODUCT&gt;
          &lt;ITEM&gt;R7876&lt;/ITEM&gt;
          &lt;NAME&gt;Gloves Pair&lt;/NAME&gt;
          &lt;STOCK&gt;No Stock.&lt;/STOCK&gt;
      &lt;/PRODUCT&gt;
      &lt;PRODUCT&gt;
          &lt;ITEM&gt;BR122293&lt;/ITEM&gt;
          &lt;NAME&gt;Silver Duo Balls&lt;/NAME&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;
      &lt;/PRODUCT&gt;
      &lt;PRODUCT&gt;
          &lt;ITEM&gt;cloneboyb&lt;/ITEM&gt;
          &lt;NAME&gt;Cast Your Own Kit&lt;/NAME&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;
      &lt;/PRODUCT&gt;
      &lt;PRODUCT&gt;
          &lt;ITEM&gt;ASTRO&lt;/ITEM&gt;
          &lt;NAME&gt;Astroglide&lt;/NAME&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;
      &lt;/PRODUCT&gt;
      &lt;PRODUCT&gt;
          &lt;ITEM&gt;3002028110&lt;/ITEM&gt;
          &lt;NAME&gt;Translucence Pink&lt;/NAME&gt;
          &lt;STOCK&gt;In Stock&lt;/STOCK&gt;
      &lt;/PRODUCT&gt;
      &lt;PRODUCT&gt;
          &lt;ITEM&gt;0340&lt;/ITEM&gt;
          &lt;NAME&gt;Cream 82g Lubricant&lt;/NAME&gt;
          &lt;STOCK&gt;No Stock.&lt;/STOCK&gt;
      &lt;/PRODUCT&gt;
  &lt;/STOCK&gt;
  </code></pre>
  
  <p>Most of what I've tried is an adaption of things I've seen elsewhere on here so I don't particularly have a starting point.</p>
  
  <p>The VBA script I'm using to apply the transform and import the new XML file is the following:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim domIn As DOMDocument30
      Dim domOut As DOMDocument30
      Dim domStylesheet As DOMDocument30
      Dim xFile As String
      Dim StyleSheet As String
      Dim NewStock As String
  
      StyleSheet = "path to XSL"
      xFile = "path to xml"
      NewStock = "path to intended csv"
      Set domIn = New DOMDocument30
  
      domIn.async = False
  
      'Open the ADO xml document
      If domIn.Load(xFile) Then
  
          'Load the stylesheet
          Set domStylesheet = New DOMDocument30
          domStylesheet.Load StyleSheet
  
      'Apply the transform
      If Not domStylesheet Is Nothing Then
          Set domOut = New DOMDocument30
          domIn.transformNodeToObject domStylesheet, domOut
  
          'Save the output
          domOut.Save NewStock
  
          'Import the saved document into Access
          'Application.ImportXML NewStock
      End If
  End If
  
  'Cleanup
  Set domIn = Nothing
  Set domOut = Nothing
  Set domStylesheet = Nothing
  
  MsgBox "done!", , "ImportXMLFromADO"
  </code></pre>
  
  <p>I look forward to seeing pointers and thank you in advance.</p>