On Fri, Nov 22, 2013 at 8:05 AM, Steven Siebert <smsi...@gmail.com> wrote:
> Hi Larry, > > I'm trying to figure out what your wanting to do with the data once its in > mysql? At first it seemed you didn't want to put it in as a lob because > you might want to query on the data in different ways (kind of an > assumption on my part, but a common reason to do this). Then, you thought > about making a very generic schema (parent-node) which really doesn't lend > itself well to this goal. > > Could you explain what you plan to do with the data in mysql and maybe a > little about the goal of the application? Is this app specific to this > data, or are you looking to build an app that can take any schema defined > XML file, ingest it, and allow you to do some work with it? > > Why is a lob not appropriate for your needs? > Yes, I will need to query the data, and yes, it's app specific to the data. The parent-node structure will meet my needs if I can figure out how to preserver the unbound multi level nodes. There will be a higher level table, which will have the file name, date received, and other identifying info that will be start of the query. The nodes will have a foreign key back to the file table. The queries will be something like "get the abcd parameter from node x/y/z from the file foo.xml from 10/10/13". I guess I can just store the x/y/z in the node table (as opposed to trying to represent z is child of y which is a child of x in 3 different tables or rows. On Nov 22, 2013 7:24 AM, "Larry Martell" <larry.mart...@gmail.com> wrote: > >> 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 >> >