My initial thought is that 300K records of something might be better off in a database than an xml file for a variety of reasons.
My next thought is that when I am presented with a QofQ limitation problem, I normally solve it by using more than one query. You stated that you can't do subqueries. You might be able to do this.
where a_field in (#valuelist(QofQ1.somefield)#)
My initial thought was "WTF?" My client receives this xml file and there is nothing he can do about the format. Actually xml is the best way to transmit this info BUT it needs to be broken up in chunks. Okay, actually the best way would be a webservice that I could tap into to pull the data over. BUT once again, I have no control over that.
The way it is working out I think there are 2 solutions:
1 - 2 tables. 1 takes the top level info the other takes just the productid and subcat. Then join them in a normal query.
2 - Put all the top level info into the product table and then update them with the subcat by looping over the subcat query. Could be a long loop but since it is done on off hours it should be okay.
I'd just hoped there was someway of pulling off a pretty join or something with QoQ but I guess not.
Not really an answer to your question re the best DB schema for the solution, but can you not suck the XML straight into the DB without involving CF? I'm not sure that using an XLS file as an intermediary DB sort of thing is a very good approach, if it can possibly be avoided.
Ditto. Go from the XML directly into your database. Your options increase by an order of magnitude once the data is in a database.