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.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:
On Thu, Jan 26, 2017 at 5:37 AM, Rita <rmorgan466@xxxxxxxxx
<mailto:rmorgan466@xxxxxxxxx>>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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
