Csdrhrt

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?

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