Import XML into one-to-many tables

[Jay Myhre]

I have been given an xml file that contains multiple records. Each record contains data that goes into "sub" tables. Example:
<Record>
<E01>
<E01_01>12345</E01_01>
<E02>


<E02_01>First</E02_01>
</E02>
<E02>
<E02_01>Second</E02_01>
</E02>
</E01>
<E01_01>12346</E01_01>
<E02>
etc, etc
When I imported the XML data it created the structure and imported the data. I have added a foreign key to the "sub" table E02, created the proper relationships between E01 and E02, deleted the data and re-imported (just the data) the XML data. It does not add the foreign key data in E02. How do I get it to place the value contained in E01_01 into the foreign key field in E02? I couldn't get the default value for the foreign key field to use the value in E01_01. Thanks for any help you can give.

[June7]

Are you using the import wizard?

Does this help http://www.utteraccess.com/forum/Xml...d#entry1944793

Do you want to provide the xml file for analysis?

[Jay Myhre]

I will try the sample code provided in the URL you provided.
I am willing to provide the xml file for analysis - how should I do that?
Thanks for your assistance.
Jay Myhre

[Jay Myhre]

I seem to have forgotten to mention a couple of items.
I have been trying to use the XML Import Wizard. Appears it is only able to handle flat file tables.
The XML I am attempting to import has one parent table, 32 child tables, and 4 grand-child tables.
Jay Myhre

[Jay Myhre]

Attached is copy of XML file I'm attempting to import. This is a prehospital patient care report submitted by a helicopter transport. I've changed the patient care information to hide the identify of the patient. Fortunately there was only one transport patient in this XML file. Future XMLs that I receive could contain dozens of records.
I have created an export series of functions that create similar XML files and that works just fine. My problem is importing similar data into my Access database.

[June7]

Did the reference I gave help?

[Jay Myhre]

Reference didn't exactly help (maybe my problem, not the reference).
The XML data (sent copy in my previous thread) needs to go into 44 tables (1 "parent", many "child", and several "grand-child" tables).
Not sure how to modify the referenced programming code to accomplish that. The code only makes reference to one table called "tblTemp4".
Still surprised that Microsoft hasn't created an XML Import Wizard to handle anything other than flat file imports.
Jay Myhre

[June7]

I just did a test with Access 2010 of importing your xml file with import wizard. The result was 46 tables, one was an ImportErrors table with one record that failed to save to Table E13, row 1, column E13_01 reason was 'field truncation' and one was Header table with one record.

I have not tested import with 2007. Perhaps this was an enhancement in 2010.

Code from referenced link is supposed to take any xml file, create tables, save records. You are right, the 'tblTemp4' reference is confusing. However, I can't get code to run the first For loop, bypasses and goes straight to MakeTable function which means field list is not built and MakeTable errors.

Your options appear to be:
1. upgrade to 2010
2. find workable VBA

[Jay Myhre]

I also ended up with 46 Access tables when I used the import wizard.
The reason for E13_01 being truncated is that field should be a "Memo" field type instead of T-255. I made the change to the field, deleted all the existing data and re-ran the import - no subsequent errors.
I guess the important question for me is did the E02 (and tables other than E01) have a foreign key field created to cause a relationship to the E01 table? Believe the value in the E02 table's "foreign key" field would be 2704.
If MS Access 2010 did actually do this then Yes, I would definitely upgrade to 2010. Otherwise, maybe what I'll do until I find a workable VBA solution is create foreign key fields for each "child" table, import one E01 record at a time, then run a function that assigns the primary key in E01 to the foreign key in the other tables. Not sure I like this approach.
Thanks again for replying to my posts - I really appreciate it.

[June7]

Sorry, I actually lost track of what the core issue was. So 2007 does do the import and issue is the lack of fk field. No, 2010 did not recognize the relationship either.

[Jay Myhre]

Thanks for the info on Access 2010 not adding foreign key fields.
Looks like I have some work to do.
Thanks for all the work you've done trying to help.