XML to Microsoft Access -- avoid losing keys

Question

I want to import an XML document into Microsoft Access 2010. Here is a sample from the XML:

<ROWSET>   
    <BIOG>
      <BIOG_NBR>    10021616    </BIOG_NBR>
      <FIRST_NAME>  Marvin      </FIRST_NAME>
      <LAST_NAME>   Rumple      </LAST_NAME>
      <ADDRESS_LIST>
        <ADDRESS_TYP>
          <ORG1>        UNICEF      </ORG1>
          <ADDRESS_TYPE>Work        </ADDRESS_TYPE>
          <LINE_2>      23322 Bovine Blvd   </LINE_2>
          <US_ZIP>      770303411           </US_ZIP>
          <CONTACT_LIST/>   
        </ADDRESS_TYP>
        <ADDRESS_TYP>
          <ORG1>        MENSA   </ORG1>
          <ADDRESS_TYPE>Leisure </ADDRESS_TYPE>
          <LINE_2>      421 Porcine Lane    </LINE_2>
          <US_ZIP>      770303411           </US_ZIP>
          <CONTACT_LIST/>
        </ADDRESS_TYP>
      </ADDRESS_LIST>
    </BIOG>

This is a well-formed XML document. It has a DTD definition document, but I remove the declaration because the Access import won't accept it. <ROWSET> is the root element. My example is simplified -- eight tables are represented in the schema.

When I import this, I get two tables, BIOG and ADDRESS_TYP. But ADDRESS_TYP doesn't show the foreign key BIOG_NBR.

How can I bring that in? Some ways that occur to me:

1. Use a text editor with regular expressions to insert <BIOG_NBR>____</BIOG_NBR> immediately after each instance of <ADDRESS_TYP>. This is inelegant, and because the file is 200Mb it may be impractical.
2. Use another XML parser to export all data to csv files. This would be my first option but I am under workplace limitations that exclude it.
3. Use some kind of transform like XSD. I've used this approach to render to HTML, but does it apply to this task? (And then, don't I still need an XML parser? Obviously yes...)
4. Use the Microsoft XML Library in VBA. (Does this apply?)

In a nutshell: Should I reach for a native XML tool, as in option #3? Is there another approach I'm missing?

Answer 1

It's too easy.

STEP 1 -- make an import of the data using the standard Access "Import XML" option. Structure only.

STEP 2 -- Go to each table and add a field that will take the missing key.

STEP 3 -- Use VBA. Add the Microsoft XML, v6.0 library. Even this spare bit of code tells me it will be simple to write the key to each table as needed.

Dim oDoc As MSXML2.DOMDocument
Dim fSuccess As Boolean
Dim oRoot As MSXML2.IXMLDOMNode
Dim oSoftkey As MSXML2.IXMLDOMNode
Dim oAttributes As MSXML2.IXMLDOMNode

Set oDoc = New MSXML2.DOMDocument

oDoc.async = False
oDoc.validateOnParse = False

fSuccess = oDoc.Load(strPath & "\specimen.xml")

If Not fSuccess Then Exit Sub

' Get the root of the XML tree.
Set oRoot = oDoc.documentElement
For Each oSoftkey In oRoot.ChildNodes

  For Each oAttributes In oSoftkey.ChildNodes

      Debug.Print oAttributes.Text
  Next
Next oSoftkey