XML import to Access Issue

[Umfriend]

Hi all,

Trying to get soem data into Access for further processing. Here is a snippet of the .xsd
<PERIODRANKING>
..<PERIOD>1</PERIOD>
..<ROUND>5</ROUND>
..<PLAYERRANKING>
.....<NAME>Jantje</NAME>
.....<SCORE>4</SCORE>
..</PLAYERRANKING>
</PERIODRANKING>

The thing is, I get two tables
- PERIODERANKING(Period, Round)
- PLAYERRANKING(Name, Score).

The relationship between the two tables is botched therefore. Is there any way to enforce Access to include some elements of the higher complex element in the lower table? Some options in the.xsd? Do I need an XSLT processor and an .xlst file? I hope not because I _will_ be lost then.

Thx, Umf.

[Rakido]

maybe that helps ..

http://www.databasejournal.com/featu...le.php/3310901

R.

[Umfriend]

Thanks but no. Had found that one earlier as well.

Anyway, I have found in the mean time that my problem is somewhat more complex but it is hard for me to explain as I do not know the actual lingo.

With the .xml I also have a .xsd. The .xsd for this looks like:

<xsd:complexType name="PeriodRankingType">
..<xsd:sequence>
....<xsd:element name="PERIOD" type="xsd:integer"/>
....<xsd:element name="ROUND" type="xsd:integer"/>
....<xsd:element name="PLAYERRANKING" type="PlayerRankingType" minOccurs="0" maxOccurs="unbounded"/>
..</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="PlayerRankingType">
..<xsd:sequence>
....<xsd:element name="NAME" type="xsd:string"/>
....<xsd:element name="SCORE" type="xsd:float"/>
..</xsd:sequence>
</xsd:complexType>

As you can see, the .xml has tags which are named without "Type" (eg. PlayerRanking, not PlayerRankingType). Access creates two tables though:
PlayerRankingType, which [i]does[i] have a foreign key to PeriodRankingType but is empty and PlayerRanking which actually has data but all fields are "Text" as that element name is not present in the .xsd.

What I have done is remove all the "Type" parts of names in the .xsd and sure enough, that works if it were not for one thing: Some ComplexTypes are used as children of more than one parent type (think of having a database where you have friends, which are persons, and emplyees, which are persons as well. In .xml/.xsd you might declare one ComplexType "Person" with all the same attributes.). And here is the catch; as both are of the same type, Access will try to store both types of Persons in the same table but require a foreign key to both possible parents (i.e. to Employees and to Friends). But, obviously, only one will be available at any time...

It may well be that the xsd and/or xml violates one or more rules, conventions or recomendations, can;t be sure. I am not in control of the creation of these files.

On the other hand, I do not see why Access should not create two child-tables (i.e. one for Employees and one for Friends) with equal structure but relationships to their respective parents. Obviously, if the structure is the same it would be normal to have one table holding both and have the foreign key fields NULLable.

As I am looking at it some more, I think I might be able to suggest some changes to the .xsd that would solve it all...

Thx anyway, much appreciated.