Re: using hstore to store documents

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 <adrian(dot)klaver(at)aklaver(dot)com>
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
>> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>>
>> On Thu, Jan 26, 2017 at 5:37 AM, Rita <rmorgan466(at)gmail(dot)com
>> <mailto:rmorgan466(at)gmail(dot)com>>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
> adrian(dot)klaver(at)aklaver(dot)com
>

--
--- Get your facts first, then you can distort them as you please.--