I have a need to store data that comes in an XML file in MySQL. I don't want to store the entire file as a CBLOB, and it doesn't lend itself to loading with Load XML. I am looking for tools that will help me create the schema and parse and load the data. I have googled for this and I've found many scholarly papers written about it, but the examples are always with very simple XML files, and I haven't found any tools. I do have the xsd schema for the XML file, but what's confusing me about how to design the RDB schema is the unbounded recursion of the data. The data is basically nodes that have parameters which have items with values. My first thought was to simply have a node table and a parameter table with a foreign key back to the node it belongs to. But when digging into the data I found that nodes can have sub nodes - in one file I have this goes down for 7 levels, but in theory there is no bound on that. I'm now sure how to best represent that.
Any experiences, advice, or pointers are very welcome. Thanks- larry