Import XML - autonumber and foreign keys

[georgesh]

I tried to post this to the Tables group but haven't had any response so far. I don't know for certain whether it is an underlying design problem or a coding one. It is long explanation for what is possibly a very basic problem.

I have recently been involved in a process to build a simple database to capture equipment faults. The reporting is conducted via a dynamic pdf form that is given to field staff to take with them and use regardless of whether or not they have an internet connection at the time of filling out the form. This is important because they therefore have no access to the database that will eventually hold their reports, and hence cannot lookup records that exist, generate unique IDs, etc.

When the form has been filled out, an xml file is created and sent. The form has a basic structure of:

<issue> *but no unique key
<datafield1/> * several simple type data fields
<datafield2/>
<majorcomponent>
<majdatafield/>
<subcomponent>
<subcomdatafield1/>
<subcomdatafield2/>
</subcomponent>
<subcomponent>
<subcomdatafield/>
<subcomdatafield/>
<subcomdatafield/>
</subcomponent>
</majcomponent>
<majorcomponent>
<majdatafield/>
<subcomponent>
<subcomdatafield1/>
<subcomdatafield2/>
</subcomponent>
<subcomponent>
<subcomdatafield/>
<subcomdatafield/>
<subcomdatafield/>
</subcomponent>
</majcomponent>
<datafield3/>
</issue>

In other words, each issue might have one or more major components and each major component may have one or more subcomponents. It is possible that a subcomponent can be present in more than one major component and each major component will show up in multiple issues.

I have created three tables.

Issue
-issueID (autonumber primary key)
...
...

MajorComponents
-issueID (foreign key) (compound primary key)
-majorComponentName (compound primary key)

Subcomponents
-issueID (foreign key) (compound primary key)
-majorComponentName (foreign key) (compound primary key)
-subcomponent name (compound primary key)
...
...
...

Because the xml file is submitted from the field, there is no unique identifier associated with it at that time. The primary key is created when it is imported into the system with very simple VBA code.

I am very rusty with databases and access and suspect I am overlooking something obvious.

My problem is that I when I try to import the data into the tables it only works when they are disconnected tables and do not have the primary key set to those listed above. The reason is simple enough: the xml file does not contain the issueID and nor does it explicitly express the relationships (so although a subcomponent will be a child node of a specific major component, this is only laid out in the hierachical structure of the xml file) but I thought that the hierarchical structure of the xml file would be sufficient to express relationships between elements.

If I use the table structure outlined above, Access will populate the Issue table and create a unique ID using the autonumber field. However, it will not append the data to the related tables since as far as it is concerned, there is no issueID or even majorComponentName fields in the child elements.

What I need to know is, using simple VBA, how to create the top level issue record by importing an xml data file and creating an autonumber key and then populating the foreign keys of the dependent tables with the newly created issueID and also fill in the second part of the primary key (majComponentName) from the parent element.

I apologise for the length of this post but hope that it makes sense. If anyone can offer any assistance, I would be very appreciative.

 

[georgesh]

I am not sure that my original posting was entirely clear. In any case, I have determined a way to link my tables together despite having no unique identifier on each submitted form. I am sure my solution is not the most elegant, and I suspect using xpath, etc. more of the functionality is built into the xml. But I couldn't figure this out.

In my VBA code I have relied on MSXML 6.0 to create a DOMDocument object instance in which to import the XML document. Then for the highest level element I create an IXMLDOMNode. For this node, I read in the values of its simpleType elements and place them in my main table (with ADODB.Connection to connect to the database and the ADODB.Recordset.Addnew method to add the record, then .Update), issues, which relies on an autonumber for its primary key field. When the record has been created, I read the new record primary key and store it in a variable.

I then go to the next level element (xml complexType), majorComponent, which is stored in a new table since it has a many-to-one relationship with the issues in the main table. In other words, for any issue there can be multiple majorComponents. Since I don't have a unique identifier in the xml, I use a combination of the element's main name value and the primary key taken from the previous main table. In this way, I now have a foreign key to the main table.

There is one further nested element in majorComponent, subComponents. Each majorComponent can have multiple subComponents. I deal with each subComponent in an iteration nested in the loop dealing with each majorComponent. So, I open the subComponent table recordset and use the stored issue primary key from earlier to place in the table for the first part of a compound primary key. I also insert the majorComponent name (taken expressly from the majorComponent parent node) and the current subComponent name as the second and third parts of the compound primary key.

Anyway, although it seems slightly convoluted and my explanation leaves much to be desired, it seems to have done the trick.