Import XML And Retain Relationships? Any Workarounds To Do So?

[Matt Kruse]

I know that importing XML into Access does not retain relationships
(which makes it nearly useless, IMO). I'm looking for work-arounds or
hacks which will make this work as expected.

My idea is below. If anyone has done this or anything like it, could
you please let me know?

I'm thinking of pre-processing the XML so Access creates additional
relationship tables. For example, given this XML data:

<person>
<id>123</id>
<name>Bob</name>
<children>
<id>456</id>
<name>Bill</name>
</children>
<person>

This wll create a PERSON table and a CHILDREN table, but not maintain
the fact that Bill is Bob's child.

If I pre-process this file, though, and tell my pre-processor (Perl)
that "id" is the unique key for person and children, then it could
create this:

<person>
<id>123</id>
<name>Bob</name>
<children>
<id>456</id>
<name>Bill</name>
</children>
<person>
<person_children>
<person_id>123</person_id>
<children_id>456</children_id>
</person_children>

This would create a many-to-many relationship table which could be
used to join the data back together. It's not perfect, since it would
create a many-to-many relationship where a 1-to-1 might be sufficient,
but either way, the data would still be valid.

Does this idea sound reasonable? My thinking is, this kind of
pre-processing would hold true for any type of data structure, and so
would be general-purpose enough that any type of XML data could be
imported and maintain relationships.

Thoughts?

Matt Kruse
http://www.mattkruse.com/