 |
|
|
Hi
I am sorry but this is going to be a bit of a lengthy post. I am new to xml
I have been supplied an xml file that contains data I need to import into a
database. If I just import the file as is I lose any relationship between the
parent and child nodes when the import routine in Access 2003 creates the
tables. I have come to the conclusion that I have two options. I must either
create an xsl file to alter the output from the xml file to preserve the
relationships or I must use the MS XML object model through VBA and iterate
the file myself to cherry pick what is needed. I have included a snippet of
the file below. The file contains UK driving licence information where we can
check a persons driving entitlement and conviction history. Each complete
check is called a Response, each response has information about the response
itself, then personal information about the subject, details about their
licence, categories of vehicle they can drive, restrictions for a particular
category and then endorsements on their licence for driving convictions.
<DQ3_Message_Group xmlns='urn:schemas.dvla.gov.uk/ABC'>
<Response>
<PersonID>683536c4</PersonID>
<AgreementID>808a5ecf</AgreementID>
<DateOfEnquiry>2005-04-18</DateOfEnquiry>
<DriverNumber>GMFOT206048RJ9VG</DriverNumber>
<Personal>
<Surname>GMFOT</Surname>
<Forenames>RICHARD JOHN</Forenames>
<Addr1>LJ DESK</Addr1>
<Addr2>FUJITSU</Addr2>
<Addr3>PHOENIX WAY</Addr3>
<Addr4>ENTERPRISE PARK</Addr4>
<PostTown>SWANSEA</PostTown>
<PostCode>SA079HW</PostCode>
<DateOfBirth>1928-06-04</DateOfBirth>
<Sex>M</Sex>
</Personal>
<Licence>
<IssueNumber>38</IssueNumber>
<Type>G</Type>
<TypeLiteral>EXPIRED SUBSTANTIVE</TypeLiteral>
<ExpiryDate>2001-06-03</ExpiryDate>
</Licence>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>A</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>B</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>BE</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>B1</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>F</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>G</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>K</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>L</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>N</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>P</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>P</Status>
<CommenceDate>1974-11-06</CommenceDate>
<ExpiryDate>2001-06-03</ExpiryDate>
<Code>H</Code>
<Identifier>NCC</Identifier>
</Category>
<Endorsement>
<OffenceDate>2002-02-27</OffenceDate>
<OffenceCode>SP30</OffenceCode>
<Points>03</Points>
</Endorsement>
</Response>
<Response>
<PersonID>683536c4</PersonID>
<AgreementID>362863bc</AgreementID>
<DateOfEnquiry>2005-04-18</DateOfEnquiry>
<DriverNumber>GMSLT202284HC9SX</DriverNumber>
<Personal>
<Surname>GMSLT</Surname>
<Forenames>HENRY CHARLES</Forenames>
<Addr1>LJ DESK</Addr1>
<Addr2>FUJITSU</Addr2>
<Addr3>PHOENIX WAY</Addr3>
<Addr4>ENTERPRISE PARK</Addr4>
<PostTown>SWANSEA</PostTown>
<PostCode>SA079HW</PostCode>
<DateOfBirth>1924-02-28</DateOfBirth>
<Sex>M</Sex>
</Personal>
<Licence>
<IssueNumber>78</IssueNumber>
<Type>Q</Type>
<TypeLiteral>SUBSTANTIVE REVOKED/REFUSED</TypeLiteral>
<ExpiryDate>2000-01-18</ExpiryDate>
</Licence>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>A</Code>
<Identifier>NCC</Identifier>
<Restriction>
<Code>8</Code>
</Restriction>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>B</Code>
<Identifier>NCC</Identifier>
<Restriction>
<Code>8</Code>
</Restriction>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>BE</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>B1</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>C1</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>C1E</Code>
<Identifier>NCC</Identifier>
<Restriction>
<Code>107</Code>
</Restriction>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>D1</Code>
<Identifier>NCC</Identifier>
<Restriction>
<Code>101</Code>
</Restriction>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>D1E</Code>
<Identifier>NCC</Identifier>
<Restriction>
<Code>101</Code>
</Restriction>
<Restriction>
<Code>119</Code>
</Restriction>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>F</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>K</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>L</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>N</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>P</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>P</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>G</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>P</Status>
<CommenceDate>1975-08-04</CommenceDate>
<ExpiryDate>2000-02-27</ExpiryDate>
<Code>H</Code>
<Identifier>NCC</Identifier>
</Category>
</Response>
<Response>
<PersonID>683536c4</PersonID>
<AgreementID>1aae516a</AgreementID>
<DateOfEnquiry>2005-04-18</DateOfEnquiry>
<DriverNumber>GMUU9602100GE9PB</DriverNumber>
<Personal>
<Surname>GMUU</Surname>
<Forenames>GARY EDWARD</Forenames>
<Addr1>LJ DESK</Addr1>
<Addr2>FUJITSU</Addr2>
<Addr3>PHOENIX WAY</Addr3>
<Addr4>ENTERPRISE PARK</Addr4>
<PostTown>SWANSEA</PostTown>
<PostCode>SA079HW</PostCode>
<DateOfBirth>1960-02-10</DateOfBirth>
<Sex>M</Sex>
</Personal>
<Licence>
<IssueNumber>03</IssueNumber>
<Type>F</Type>
<TypeLiteral>SUBSTANTIVE</TypeLiteral>
<ExpiryDate>2030-02-09</ExpiryDate>
</Licence>
<Category>
<Status>F</Status>
<CommenceDate>1977-10-01</CommenceDate>
<ExpiryDate>2030-02-09</ExpiryDate>
<Code>P</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<CommenceDate>1977-10-01</CommenceDate>
<ExpiryDate>2030-02-09</ExpiryDate>
<Code>B</Code>
<Identifier>NCC</Identifier>
</Category>
<Category>
<Status>F</Status>
<ExpiryDate>2030-02-09</ExpiryDate>
<Code>A</Code>
<Identifier>NCC</Identifier>
</Category>
<Endorsement>
<OffenceDate>2003-09-10</OffenceDate>
<OffenceCode>TS20</OffenceCode>
<Points>03</Points>
</Endorsement>
<Endorsement>
<OffenceDate>2004-04-23</OffenceDate>
<OffenceCode>SP50</OffenceCode>
<Points>03</Points>
</Endorsement>
</Response>
</DQ3_Message_Group>
Many of these relationships are 1 to many so I need to build Primary and
Foreign Keys from the available data.
The top level Primary Key for each <Response> would be PersonID, the
relationship to <Personal> is 1 to 1 so the same Primary Key applies, the
same applies to <Licence>.
<Category> is the first 1 to many relationship, here the Primary Key could
be PersonID and Code
<Restriction> is the second 1 to many relationship, in this cae the Primary
Key could be PersonID and Code and Code.
<Endoresement> is a 1 to many but this time Endorsement itself cannot have a
Primary Key, just a Foreign Key of PersonID.
Can anybody point me in the direction of some good examples of how to do
this. I have bought a book Beginning XSLT 2.0 by Jeni Tennison which I will
study but I need to get some quick results.
Please help.
Kind regards
Tim
|
|
| |
These Archives are provided for informational purposes only and have been generated directly from the Altova mailing list archive system and are comprised of the lists set forth on www.altova.com/list/index.html. Therefore, Altova does not warrant or guarantee the accuracy, reliability, completeness, usefulness, non-infringement of intellectual property rights, or quality of any content on the Altova Mailing List Archive(s), regardless of who originates that content. You expressly understand and agree that you bear all risks associated with using or relying on that content. Altova will not be liable or responsible in any way for any content posted including, but not limited to, any errors or omissions in content, or for any losses or damage of any kind incurred as a result of the use of or reliance on any content. This disclaimer and limitation on liability is in addition to the disclaimers and limitations contained in the Website Terms of Use and elsewhere on the site.
| |
| |
|
|
|
