Lists Home |
Date Index |
And I want to create an xslt stylesheet to transform the XML into SQL
statements for the tables:
I can assume the tables are empty
Which is the best approach? obviously the problem is dealing with ids, you
have to mantain the id for each member of the
department and "increment" ids as you create new departments/ members.
A lot depends on whether you will just run this code once to seed the
tables, and after that all changes will be done through the database, or
whether you may re-populate the tables from xml files with different data.
At least, this is important if your xml-generated tables will end up being
linked to by other tables, because the other tables need to have stable
keys, but they might not get generated by the stylesheet in the same run.
If all tables will be generated from the xml in one run, the best bet is to
use generate-id(). Then each element will have a unique id, and you can get
that same id each place you need to use it in creating the DDL for several
tables (in the same run of the stylesheet). In the database, you will have
to make the id field some string or character type, since most xslt
processors give you a string for the id.
You could use postition(), but you would probably have trouble making sure
that all references to the same thing (dept, member, etc) gave the same
value each place they were used. That is why generate-id() is better.
If the tables will be linked to by other tables created separately, then the
original tables repopulated from new XML, you could have a very difficult
problem whose solution would have to be worked out on a case-by-case basis.
You might have to export the entiure database to xml and then get clever
about combining the new and old data. XQuery, when it finally gains update
capability (in a few years?) should help with this problem.