title: Access XML import: Add foreign key to child table
body: <p>I have An XML File that I want to import in MS-access. The problem is that it has multiple nested nodes which means access creates a new table for every different type of node. Now the problem is we cannot create any relationship between different tables, We can't even decipher their relationship in the database. Is there is anyway to solve this problem? The xml is generated by a software I use. A sample XML Data that will cause the type of problem I am facing:</p>
  
  <pre><code>&lt;Envelope&gt;
      &lt;Sale&gt;
          &lt;CustomerName&gt;a&lt;/CustomerName&gt;
          &lt;InvoiceNo&gt;1&lt;/InvoiceNo&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I1&lt;/ItemName&gt;
              &lt;QTY&gt;10&lt;/QTY&gt;
              &lt;Amount&gt;25&lt;/Amount&gt;
          &lt;/item&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I2&lt;/ItemName&gt;
              &lt;QTY&gt;15&lt;/QTY&gt;
              &lt;Amount&gt;14&lt;/Amount&gt;
          &lt;/item&gt;
      &lt;/Sale&gt;
      &lt;Sale&gt;
          &lt;CustomerName&gt;b&lt;/CustomerName&gt;
          &lt;InvoiceNo&gt;2&lt;/InvoiceNo&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I3&lt;/ItemName&gt;
              &lt;QTY&gt;10&lt;/QTY&gt;
              &lt;Amount&gt;72&lt;/Amount&gt;
          &lt;/item&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I4&lt;/ItemName&gt;
              &lt;QTY&gt;21&lt;/QTY&gt;
              &lt;Amount&gt;43&lt;/Amount&gt;
          &lt;/item&gt;
      &lt;/Sale&gt;
      &lt;Sale&gt;
          &lt;CustomerName&gt;c&lt;/CustomerName&gt;
          &lt;InvoiceNo&gt;3&lt;/InvoiceNo&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I5&lt;/ItemName&gt;
              &lt;QTY&gt;13&lt;/QTY&gt;
              &lt;Amount&gt;53&lt;/Amount&gt;
          &lt;/item&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I6&lt;/ItemName&gt;
              &lt;QTY&gt;37&lt;/QTY&gt;
              &lt;Amount&gt;27&lt;/Amount&gt;
          &lt;/item&gt;
      &lt;/Sale&gt;
      &lt;Sale&gt;
          &lt;CustomerName&gt;d&lt;/CustomerName&gt;
          &lt;InvoiceNo&gt;4&lt;/InvoiceNo&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I7&lt;/ItemName&gt;
              &lt;QTY&gt;45&lt;/QTY&gt;
              &lt;Amount&gt;67&lt;/Amount&gt;
          &lt;/item&gt;
          &lt;item&gt;
              &lt;ItemName&gt;I8&lt;/ItemName&gt;
              &lt;QTY&gt;12&lt;/QTY&gt;
              &lt;Amount&gt;98&lt;/Amount&gt;
          &lt;/item&gt;
      &lt;/Sale&gt;
  &lt;/Envelope&gt;
  </code></pre>