by Here is a quick rundown on how things currently work:
Data is recevied from various places in the world, using various databases.
(Outof my control, I have to live with it.) However, all data is receive in
a very specific XML format, and is uploaded to an FTP server.
The received data structure is checked against a DTD for sanity.
If the sanity check passes then SQL picks up the data.
The problem that we are having is the fact that XML/DTD is too rigid. I need
to build in a little wiggle room for some of the fields. Example:
Field name: MyChoice - can be foo or bar only. (This is the current format.)
I need MyChoice to take whatever the endusers are dumping into it. Right
now, everytime someone needs a new choice we have to add it to the DTD.
Which is a PITA. The other problem with the DTD is best explained by this
example:
Record 1 foo Nancy green
Record 2 bar Nancy blue
Record 3 foo Bill green
Record 4 bar Nancy Red
The DTD validation will reject this record set since one of the Nancy's came
after a Bill. In other words, the Nancy's all need to be together. I realize
that the DTD could more than likely be massaged to take care of a lot of
this, but the ultimate question is: Is there a better way to validate the
data structure, and import into SQL other than XML?