I want to over write the data because I don't need to keep its history. Yes, the 5-6 table will be the subset of the data. The subset of the data will all come from xpath. I don't want to keep doing an xpath query when a client requests the data.
On Sun, Jan 29, 2017 at 5:42 PM, Adrian Klaver <[email protected]> wrote: > On 01/28/2017 05:57 PM, Rita wrote: > >> sorry for the late reply. >> >> My table schema is very simple >> >> DROP TABLE xmltest; >> >> >> >> >> >> create table xmltest( >> >> >> id serial, -- dont really need the serial >> > > Maybe not a serial id, but a Primary Key of some sort would help with what > you say you want to do below, I think. > > >> >> data xml NOT null >> >> >> ); >> >> >> >> >> >> >> >> >> >> >> >> INSERT INTO xmltest (data, id) VALUES (' >> >> >> <attendee> >> >> >> <bio> >> >> >> <name>John Doe</name> >> >> >> <birthYear>1986</birthYear> >> >> >> </bio> >> >> >> <languages> >> >> >> <lang level="5">php</lang> >> >> >> <lang level="4">python</lang> >> >> >> <lang level="2">java</lang> >> >> >> </languages> >> >> >> </attendee>', 1); >> >> I really don't need the serial but every 30 seconds or so I plan to >> overwrite the data portion. so, I suppose I wanted a simple key/value >> thats where my orignal question stemmed from. >> > > Why do you want to overwrite the data if you plan to refer to it below? > > >> After xmltest has been populated, I can run xpath and unest to get my >> data into a row but I would like to store that result in another table, >> I am guessing I should look into triggers for something like that? >> Eventually, I plan to have 5-6 downstream tables which will have xmltest >> > > Why 5-6 tables? > > Are they each holding some subset of data? > > A schematic representation of what you are thinking of doing would help > with developing an answer to your question. > > as my head. The application will be accessing the downstream tables and >> rarely be touching xmltest (head table). >> >> >> >> >> On Thu, Jan 26, 2017 at 10:38 AM, David G. Johnston >> <[email protected] <mailto:[email protected]>> wrote: >> >> On Thu, Jan 26, 2017 at 5:37 AM, Rita <[email protected] >> <mailto:[email protected]>>wrote: >> >> of course, sorry for being vague. >> >> I have an external process generating a XML file (every 30 secs) >> which is about 10MB. I would like to store the file as XML type >> for me to query using xpath. I plan to query it every few >> seconds by few hundred clients. so, it maybe easier for me >> create a separate table of my xpath results and have clients >> query that table (xpath can be expensive). >> >> >> If the XML being generated has a fixed structure/schema I >> personally would treat the XML as a serialization format and >> de-serialize and store it in a database as one or more relationally >> linked tables. If you have to deal with the possibility of dynamic >> structure I would still try to put the fixed items into individual >> columns and then and then any dynamic items could be stuffed into an >> hstore typed table. >> >> My answer to your stated question is: what happened when you tried >> doing that? Documentation and a bit of experimentation goes a long >> ways in learning. >> >> David J. >> >> >> >> >> -- >> --- Get your facts first, then you can distort them as you please.-- >> > > > -- > Adrian Klaver > [email protected] > -- --- Get your facts first, then you can distort them as you please.--
