Import data from a XML file

[teja]

Hi all,

I have data in a XML file. I have to import the data into the Access database. In the XML file each node has 13 child nodes. Hence, when I try to import the data, 13 new tables are formed.

Since no 2 tables have a common column I am finding it difficult to merge the data into 1 big master table.

Consider the example if 2 students are enrolled into the 6 subjects each and the first tab contains the list of students and the second tab contains the list of subjects, then while importing 2 tables are formed. The first table has 2 rows of students and the second table has 12 rows of their corresponding subjects. If I were to create a master table having students and their corresponding subjects I think I need a way to recognize the students with their corresponding subject using some unique identifier. Here we know the number of subjects hence we can split it into 6 and 6. but if the number of subjects is unknown then i think we need a unique identifier.



My question - Is there any other way to make the master table with the corresponding student data without the unique identifier if the number of subjects each takes in variable.

[June7]

Students should be in one table and their subjects should be another table. The subject table needs a field that has the student ID. I have never imported XML. If the primary/foreign key fields are not generated by the import, how can you ever determine which subject records go with which students?

Do you want to provide file with dummy data (nothing confidential) for analysis?

[teja]

Using XSL file to transform the XML file helped in splitting the data into appropriate tables.