Re: [GENERAL] xml-file as foreign table?

[Johann Spies]

Thanks Arjen,



> def q(v):
>  return b'"' + v.replace(b'"', b'""') + b'"'
>
> return b','.join(q(f) for f in fields) + b'\n'
>
> In the end I also had some other problems with the XML (namespaces), so I
> used:
>
> etree.tostring(element, method='c14n', exclusive=True)
>

This helped. My code is now doing it's job.

Regards
Johann

Re: [GENERAL] xml-file as foreign table?

[Arjen Nienhuis]

On Thu, May 5, 2016 at 2:13 PM, Johann Spies  wrote:
> Dankie Arjen,
>
> On 29 April 2016 at 07:01, Arjen Nienhuis  wrote:
>
>>
>> > The options I am considering is :
>> >
>> > 1. Unpack the individual records (will be more than 50 million) using
>> > something like python with lxml and psycopg2 and insert them after dropping
>> > all indexes and triggers on the table
>> >
>> > 2. Unpack the individual records and write a (very) large tsv-file and
>> > then insert it using 'copy'
>> >
>>
>> The fastest way I found is to combine these two. Using iterparse from lxml
>> combined with load_rows and COPY from py-postgresql:
>>
>> http://python.projects.pgfoundry.org/docs/1.1/driver.html#copy-statements
>>
>> That way you can stream the data.
>
>
> I did not know about py-postgresql as I am a Python-2.7 user.
>
> I am excited with the possiilities python-postgresql is offering. After a
> bit of experimenting to use the streaming-copy option I landed up in the
> same problem that I had while using python2: Postgresql rejecting the
> xml-part of the record.
>
> How do you handle the conversion from string to bytes and back and the
> presence of quotes within the xml?
>
> I have tried this to experiment with just 10 records for a start:
>
> ut = element.xpath('.//t:UID/text()',namespaces=namespaces)[0]
> x = etree.tostring(element)
> y = x.decode(encoding='UTF-8').replace("'", "''")
> s  = '\t'.join([gzipf,filename, ut,y])
> t =  s.encode(encoding='UTF-8')
> rows.append(t)
> count += 1
> element.clear()
> gc.collect()
> if count == 10:
> import pdb;pdb.set_trace()
> xmlin.load_rows(rows)
> rows = []
> f.close()
> exit(0)
>
> Which ends up with an error:
>
> postgresql.exceptions.XMLContentError: invalid XML content
>   CODE: 2200N
>   LOCATION: File 'xml.c', line 1551, in xml_ereport from SERVER
>   CONTEXT: COPY annual, line 1, column xml: " xmlns="http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord;
> r_id_disclaimer="R..."
>   DETAIL: line 1: Premature end of data in tag REC line 1

If I need to guess it's because of \n in the xml. I used "WITH (FORMAT
csv)" and quote the fields:

def q(v):
 return b'"' + v.replace(b'"', b'""') + b'"'

return b','.join(q(f) for f in fields) + b'\n'

In the end I also had some other problems with the XML (namespaces), so I used:

etree.tostring(element, method='c14n', exclusive=True)

>
> With python2.7 I tried to use the same technique using subprocess and a call
> to psql to pipe the data to Postgresql -- ending with the same error.
>

Maybe you can show a few lines of the output.

> Dankie nogmaals vir die verwysing na python-postgresql.
>
> Mooi dag.
>
> Johann
>

Groeten, Arjen


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml-file as foreign table?

[Arjen Nienhuis]

On Apr 28, 2016 14:33, "Johann Spies"  wrote:
>
> I have several large (7GB+) xml files to get into an SQL database.
>
> The xml-files can contain up to 500 000 subrecords which I want to be
able to query in the database.
>
> They are too large to do something like this:
>
>
> insert into rawxml (xml) select XMLPARSE (DOCUMENT
CONVERT_FROM(PG_READ_BINARY_FILE('FOO.xml' ), 'UTF8'));
>
> If it were possible, each file would be one huge record in the table
which can then be unpacked using XPATH.
>
>
> The options I am considering is :
>
> 1. Unpack the individual records (will be more than 50 million) using
something like python with lxml and psycopg2 and insert them after dropping
all indexes and triggers on the table
>
> 2. Unpack the individual records and write a (very) large tsv-file and
then insert it using 'copy'
>

The fastest way I found is to combine these two. Using iterparse from lxml
combined with load_rows and COPY from py-postgresql:

http://python.projects.pgfoundry.org/docs/1.1/driver.html#copy-statements

That way you can stream the data.

> It would be convenient If I could use the present xml files as 'foreign
tables'  and parse them using the xpath-capabilities of PostgreSQL.
>
> Is this possible?
>

There is a multicorn fdw for that:

https://github.com/Kozea/Multicorn/blob/master/

python
/

multicorn
/

xmlfdw.py


But I never tried it. It looks like it loads all rows in a python list.

Groeten, Arjen

[GENERAL] xml-file as foreign table?

[Johann Spies]

I have several large (7GB+) xml files to get into an SQL database.

The xml-files can contain up to 500 000 subrecords which I want to be able
to query in the database.

They are too large to do something like this:


insert into rawxml (xml) select XMLPARSE (DOCUMENT
CONVERT_FROM(PG_READ_BINARY_FILE('FOO.xml' ), 'UTF8'));

If it were possible, each file would be one huge record in the table which
can then be unpacked using XPATH.


The options I am considering is :

1. Unpack the individual records (will be more than 50 million) using
something like python with lxml and psycopg2 and insert them after dropping
all indexes and triggers on the table

2. Unpack the individual records and write a (very) large tsv-file and then
insert it using 'copy'

It would be convenient If I could use the present xml files as 'foreign
tables'  and parse them using the xpath-capabilities of PostgreSQL.

Is this possible?

Of not, what would be the most efficient way of getting the individual
xml-records xml into a table into a xml-type field?

The documentation did not give me a clear answer.

Regards
Johann
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: [GENERAL] xml

[Pavel Stehule]

2015-03-23 12:40 GMT+01:00 Ramesh T rameshparnandit...@gmail.com:

 Hi all,
  SELECT  xmlagg(xmlelement(
   name actor, xmlattributes(first_name)
 )ORDER BY actor_id,',')from actor;

 the above code return following result,

 [image: Inline image 1]
 Question :

 i want retrieve  result from above XML result like
 penelope,nick,jennifer,jhony,


result of xmlagg is not valid xml.

if you use xmlelement, then you can get valid xml

ostgres=# select * from x;
   a
---
 dactor first_name=Pavel/actor first_name=Tomas//d
(1 row)

postgres=# select unnest(xpath('//actor/@first_name',a)::text[]) from x;
 unnest

 Pavel
 Tomas
(2 rows)

Regards

Pavel



 xpath is their but unable to fix  this query.

 any help how to get only names from query not xml.

Re: [GENERAL] xml

[Pavel Stehule]

2015-03-23 15:09 GMT+01:00 Tom Lane t...@sss.pgh.pa.us:

 Pavel Stehule pavel.steh...@gmail.com writes:
  result of xmlagg is not valid xml.

 Really?  Either that's a bug, or it's declared wrong.


I was not accurate. tag /tag / is not valid xml document - and xpath
function doens't like it.

postgres=# select xpath('//tag/@x','tag x=x/tag /'::xml);
ERROR:  could not parse XML document
DETAIL:  line 1: Extra content at the end of the document
tag x=x/tag /
^
CONTEXT:  SQL function xpath statement 1
postgres=# select xpath('//tag/@x','xtag x=x/tag //x'::xml);
 xpath
---
 {x}
(1 row)

postgres=# select 'tag x=x/tag /'::xml;
 xml
-
 tag x=x/tag /
(1 row)


Regards

Pavel



 regards, tom lane

[GENERAL] xml

[Ramesh T]

Hi all,
 SELECT  xmlagg(xmlelement(
  name actor, xmlattributes(first_name)
)ORDER BY actor_id,',')from actor;

the above code return following result,

[image: Inline image 1]
Question :

i want retrieve  result from above XML result like
penelope,nick,jennifer,jhony,


xpath is their but unable to fix  this query.

any help how to get only names from query not xml.

Re: [GENERAL] xml

[Tom Lane]

Pavel Stehule pavel.steh...@gmail.com writes:
 result of xmlagg is not valid xml.

Really?  Either that's a bug, or it's declared wrong.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] {xml}

[Ramesh T]

Hello,
  when i ran following  query on postgres 9.3,
SELECT  xmlagg(xmlelement(name e,part_id||',')) from part;

result
..
{xml}

how to get part_id's..? please let me know ..
advance thanks,
R..

Re: [GENERAL] {xml}

[David G Johnston]

Ramesh T wrote
 Hello,
   when i ran following  query on postgres 9.3,
 SELECT  xmlagg(xmlelement(name e,part_id||',')) from part;
 
 result
 ..
 {xml}
 
 how to get part_id's..? please let me know ..
 advance thanks,
 R..

Are you using psql?  What version of PostgreSQL?

I am guessing {xml} is the client's way of saying that what you have is
XML.  Try casting the final result to text and see what you get.

Otherwise...

version
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5) 4.6.3, 64-bit

Since this:

WITH vals (ids) AS (
VALUES ('1'),('2')
)
SELECT xmlagg(xmlelement(name tag, ids))::text FROM vals;

works probably need to provide a self-contained example for someone to look
at.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/xml-tp5814076p5814126.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML validation of whitespace values

[Peter Eisentraut]

On 3/14/14, 11:12 AM, Tim Kane wrote:
 clone=# select xml_is_well_formed(' ');
  xml_is_well_formed
 
  t
 (1 row)
 
 
 clone=# select xpath_exists (‘//test', ' ');
 ERROR:  could not parse XML document
 DETAIL:  line 1: Start tag expected, '' not found

There are several issues at work here:

- contrib/xml2 has a slightly different notion of what is an OK xml
value than the built-in xml type.

- A string consisting of whitespace is well-formed XML content, but not
a well-formed XML document.  Compare xmlparse(document ' ') vs
xmlparse(content ' ').  contrib/xml2 (apparently) follows the latter
interpretation.

- xpath queries only work properly on XML documents.

It might be better if you wrote your code without using contrib/xml2,
and instead caught any parse exceptions in, say, plpgsql code.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML validation of whitespace values

[David G Johnston]

Tim Kane wrote
 clone=# create temp table xml_test (document xml);
 CREATE TABLE

If you know you need to use xpath on this content then you should do one of
the following:

SELECT CASE WHEN document IS DOCUMENT THEN xpath(...) ELSE
default_value_for_missing_data END;

CREATE TABLE xml_test (
document xml NOT NULL CHECK (document IS DOCUMENT)
);

For better and worse in the name of simplicity both xml and json(b?) types
are usable for both the embedded content and the entire document.  Unless
you think you have a good reason for a single column to represent both
content and documents you should probably constrain stored data to be
whichever you are expecting.  This is the root of your confusion since, as
Peter said, xpathing only operates on documents - not content fragments.

The second argument must be a well formed XML document. In particular, it
must have a single root node element.

Unfortunately the decision to treat documents and content as the same types
means this cannot be discovered at parse-time but is data dependent and thus
a run-time error.  By putting in the constraint you ensure that any use of
said column in an xpath query will either always succeed or always fail.

Furthermore:

xml_is_well_formed does the former if the xmloption configuration parameter
is set to DOCUMENT, or the latter if it is set to CONTENT - the default for
xmloption is CONTENT...

Both quotes above come from:

http://www.postgresql.org/docs/9.3/interactive/functions-xml.html

David J.







--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/XML-validation-of-whitespace-values-tp5796092p5803594.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] xml question

[David Ekren]

I am new to this forum. I need to return the value 1 between the orientationId 
tags in this xml string within the function below. I still get errors. I'm sure 
I am doing something wrong. Any help would be appreciated.


CREATE FUNCTION Davidxml9(v_clipId integer, v_orientationId varchar(1024), OUT 
clipId int, OUT orientationId varchar(1024)) AS $$
BEGIN
clipId := v_clipId;
orientationId := xpath('/Rows/Row/orientationId[1]/text()', content)
FROM (SELECT v_orientationId ::xml AS orientationId);
  
END;
$$ LANGUAGE plpgsql;
GO

SELECT Davidxml9(1,'RowsRoworientationId1/orientationId/Row/Rows')

Re: [GENERAL] xml question

[David G Johnston]

David Ekren wrote
 I am new to this forum. I need to return the value 1 between the
 orientationId tags in this xml string within the function below. I still
 get errors. I'm sure I am doing something wrong. Any help would be
 appreciated.
 
 
 CREATE FUNCTION Davidxml9(v_clipId integer, v_orientationId varchar(1024),
 OUT clipId int, OUT orientationId varchar(1024)) AS $$
 BEGIN
 clipId := v_clipId;
 orientationId := xpath('/Rows/Row/orientationId[1]/text()', content)
 FROM (SELECT v_orientationId ::xml AS orientationId);
   
 END;
 $$ LANGUAGE plpgsql;
 GO
 
 SELECT Davidxml9(1,'
 Rows
 Row
 orientationId
 1
 /orientationId
 /Row
 /Rows
 ')

1. Try solving your xpath problem without using a custom function.
2. Go and read the documentation on writing plpgsql functions a couple of
times.  What you have written here has at least six major problems.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/xml-question-tp5801793p5801819.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML validation of whitespace values

[Tim Kane]

HI all,

I’ve had an interesting problem trying to perform an UPDATE based on the
results of processing an xpath against a field of type xml.
Specifically, my query would fail with:

 ERROR:  could not parse XML document
 DETAIL:  line 1: Start tag expected, '' not found


I thought this strange, since the xml type is (I believe) supposed to
guarantee that the value is well formed (wether it’s a document or content).

After some amount of digging to locate the rogue entry, it turns out the
field value is actually a single whitespace character – which apparently is
well formed..

Thus I’m left with the following situation…



clone=# select xml_is_well_formed(' ');
 xml_is_well_formed

 t
(1 row)


clone=# select xpath_exists (‘//test', ' ');
ERROR:  could not parse XML document
DETAIL:  line 1: Start tag expected, '' not found



clone=# create temp table xml_test (document xml);
CREATE TABLE
clone=# insert into xml_test values (' ');
INSERT 0 1
clone=# select xml_is_well_formed(document::text) from xml_test;
 xml_is_well_formed

 t
(1 row)

clone=# select xpath_exists ('test', document) from xml_test;
ERROR:  could not parse XML document
DETAIL:  line 1: Start tag expected, '' not found



This is confusing, since… if the xml is well formed – it probably shouldn’t
cause the parser to error.  And if it isn’t well formed, then the
instantiation of the value as an xml type should fail.

Re: [GENERAL] XML Schema for PostgreSQL database

[Guillaume Lelarge]

On Sun, 2012-12-16 at 22:25 -0200, Edson Richter wrote:
 Em 16/12/2012 20:27, Guillaume Lelarge escreveu:
  On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote:
  Em 14/12/2012 12:21, Merlin Moncure escreveu:
  On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com 
  wrote:
  Em 13/12/2012 20:10, Merlin Moncure escreveu:
  [...]
  *) diagram output should be standard html (only) without requiring
  tool to log in and adjust diagram
  I don't really care about output - SVG would be excellent, because it
  scales well and will print nicely.
  I can tell that a data dictionary with diagramming output would be
  enough. If I can edit the data dictionary and system keep track of all
  changes, would be enough for me.
  If PgAdmin would have the ability to maintain a data dictionary that is
  not the database itself, and plus the feature to synchronize with
  database (which would be any PostgreSQL database registered) would be
  optimal for me.
 
  We had something pretty good like this called the Database Designer.
  There are still some works to make it available on a next release.
 
  You can also try pgModeler. Seems really cool. Too bad it doesn't (yet?)
  do reverse engineering.
 
 
 
 I'm looking forward for pgModeler. Reverse and Forward engineering (with 
 true alter scripts) are an requirement for me.
 Visual design is desirable, but not mandatory.
 Where can I look Database Designer?

It's part of pgAdmin 1.16, but you need to compile pgAdmin with a
specific command line switch.

  Which platform (or language it's written)?

Platform: Linux, Windows, Mac OS X, and even Solaris IIRC.
Language: C++

 Maybe I can help with development, why start a new effort?
 

Sure, I'll be happy to have some help on this.

 I've strong Java programming expertise ( 15yrs), and also have worked 
 with dozen different languages before.
 

pgAdmin is written in C++ and uses the wxWidgets toolkit. So no Java
here, sorry.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Thomas Kellerer]

Edson Richter wrote on 14.12.2012 00:52:

I was wondering to create a tool for diagramming and database forward 
engineering.

There are already few tools around.

If you know a good diagramming tool able to database diff and forward engineering (with ALTER 
..., not DROP and CREATE), I would like to know (by today I do use one commercial 
tool that is feature incomplete: DbWrench).

Among others, I've considered also:
- Sybase PowerDesigner: too expensive, does not support PostgreSQL 9.1/9.2, so 
is not appropriate.
- ERWin: too expensive, and doesn't have proper support for PostgreSQL 9.1/9.2.
- NaviCat: is feature extensive, but they don't have real change scripts (are 
drop/create).
- ModelRight: it's change script is not change at all (is just another 
drop/create tool).
- TORA and other open source tools are really incomplete.
- TOAD is too confuse for simple day-by-day work.



Do you mean TOAD the SQL Tool or TOAD Data Modeler (TDM)? TDM is far from 
perfect but quite nice actually.

You might also want to look at DbSchema: http://www.dbschema.com/

Thomas





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Em 17/12/2012 20:14, Thomas Kellerer escreveu:

Edson Richter wrote on 14.12.2012 00:52:
I was wondering to create a tool for diagramming and database forward 
engineering.


There are already few tools around.

If you know a good diagramming tool able to database diff and forward 
engineering (with ALTER ..., not DROP and CREATE), I would like 
to know (by today I do use one commercial tool that is feature 
incomplete: DbWrench).


Among others, I've considered also:
- Sybase PowerDesigner: too expensive, does not support PostgreSQL 
9.1/9.2, so is not appropriate.
- ERWin: too expensive, and doesn't have proper support for 
PostgreSQL 9.1/9.2.
- NaviCat: is feature extensive, but they don't have real change 
scripts (are drop/create).
- ModelRight: it's change script is not change at all (is just 
another drop/create tool).

- TORA and other open source tools are really incomplete.
- TOAD is too confuse for simple day-by-day work.



Do you mean TOAD the SQL Tool or TOAD Data Modeler (TDM)? TDM is far 
from perfect but quite nice actually.


TOAD Data Modeler.



You might also want to look at DbSchema: http://www.dbschema.com/


Thanks, I have tried that also. It's nice, but it is not a true forward 
engineer (drop and create can't be called that, and the DbSchema does 
not generate true change scripts - then it requires extra effort 
reimporting data after changes - and depending on the change, it can't 
even be applied by drop/create).


Regards,

Edson



Thomas









--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Guillaume Lelarge]

On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote:
 Em 14/12/2012 12:21, Merlin Moncure escreveu:
  On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com 
  wrote:
  Em 13/12/2012 20:10, Merlin Moncure escreveu:
 [...]
 
  *) diagram output should be standard html (only) without requiring
  tool to log in and adjust diagram
 
 I don't really care about output - SVG would be excellent, because it 
 scales well and will print nicely.
 I can tell that a data dictionary with diagramming output would be 
 enough. If I can edit the data dictionary and system keep track of all 
 changes, would be enough for me.
 If PgAdmin would have the ability to maintain a data dictionary that is 
 not the database itself, and plus the feature to synchronize with 
 database (which would be any PostgreSQL database registered) would be 
 optimal for me.
 

We had something pretty good like this called the Database Designer.
There are still some works to make it available on a next release.

You can also try pgModeler. Seems really cool. Too bad it doesn't (yet?)
do reverse engineering.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Em 16/12/2012 20:27, Guillaume Lelarge escreveu:

On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote:

Em 14/12/2012 12:21, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote:

Em 13/12/2012 20:10, Merlin Moncure escreveu:
[...]

*) diagram output should be standard html (only) without requiring
tool to log in and adjust diagram

I don't really care about output - SVG would be excellent, because it
scales well and will print nicely.
I can tell that a data dictionary with diagramming output would be
enough. If I can edit the data dictionary and system keep track of all
changes, would be enough for me.
If PgAdmin would have the ability to maintain a data dictionary that is
not the database itself, and plus the feature to synchronize with
database (which would be any PostgreSQL database registered) would be
optimal for me.


We had something pretty good like this called the Database Designer.
There are still some works to make it available on a next release.

You can also try pgModeler. Seems really cool. Too bad it doesn't (yet?)
do reverse engineering.




I'm looking forward for pgModeler. Reverse and Forward engineering (with 
true alter scripts) are an requirement for me.

Visual design is desirable, but not mandatory.
Where can I look Database Designer? Which platform (or language it's 
written)?

Maybe I can help with development, why start a new effort?

I've strong Java programming expertise ( 15yrs), and also have worked 
with dozen different languages before.


Regards,

Edson



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Merlin Moncure]

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote:
 Em 13/12/2012 20:10, Merlin Moncure escreveu:

 On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com
 wrote:

 Has anyone created a XML Schema that would represent PostgreSQL database
 with all (or at least, major) structures?

 no -- furthermore, why would you want to?  what would be the consumer
 of this 'schema'?

 merlin



 I was wondering to create a tool for diagramming and database forward
 engineering.

 There are already few tools around.

 If you know a good diagramming tool able to database diff and forward
 engineering (with ALTER ..., not DROP and CREATE), I would like to know
 (by today I do use one commercial tool that is feature incomplete:
 DbWrench).

 Among others, I've considered also:
 - Sybase PowerDesigner: too expensive, does not support PostgreSQL 9.1/9.2,
 so is not appropriate.
 - ERWin: too expensive, and doesn't have proper support for PostgreSQL
 9.1/9.2.
 - NaviCat: is feature extensive, but they don't have real change scripts
 (are drop/create).
 - ModelRight: it's change script is not change at all (is just another
 drop/create tool).
 - TORA and other open source tools are really incomplete.
 - TOAD is too confuse for simple day-by-day work.

 Most of these tools or doesn't support PostgreSQL features (are too
 generic), or doesn't do real forward engineer (are only able to drop/create
 objects, not alter them), or cannot deal with partial diagrams (I can't deal
 with only one diagram with hundred of tables at once).

Years ago I decided that the only way to do forward engineering was to
capture the changes I make to development databases in scripts and to
manually apply those scripts for release management.  This process
works and like you I've found the various commercial tools to have
various weaknesses.  So for forward engineering I say: quit using
tools and write scripts.

I'm also like you amazed how poor the various database diagramming
tools are -- they all suck.  Case Studio used to be pretty good back
in the day but I wouldn't recommend it today.  My personal take on
ERD/diagramming is that:

*) diagram generation should be automatic and useful

*) human input to adjust the layout should not be required (every time
I move the stupid boxes and straighten the stupid lines I feel like
I'm finger painting)

*) diagram tool should follow database changes and adjust the diagram
accordingly

*) diagram output should be standard html (only) without requiring
tool to log in and adjust diagram

I've come around to the point of view that this is an unfilled niche
in the industry.  Furthermore, as long as scope is kept reasonably
down, this is not a very difficult project.  So I've decided (along
with Atri) to give it a shot.  Iniitial plans is to do plain html
dumps directly out of the database and use GraphVis to document
dependency flow.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Em 14/12/2012 12:21, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote:

Em 13/12/2012 20:10, Merlin Moncure escreveu:


On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com
wrote:

Has anyone created a XML Schema that would represent PostgreSQL database
with all (or at least, major) structures?

no -- furthermore, why would you want to?  what would be the consumer
of this 'schema'?

merlin



I was wondering to create a tool for diagramming and database forward
engineering.

There are already few tools around.

If you know a good diagramming tool able to database diff and forward
engineering (with ALTER ..., not DROP and CREATE), I would like to know
(by today I do use one commercial tool that is feature incomplete:
DbWrench).

Among others, I've considered also:
- Sybase PowerDesigner: too expensive, does not support PostgreSQL 9.1/9.2,
so is not appropriate.
- ERWin: too expensive, and doesn't have proper support for PostgreSQL
9.1/9.2.
- NaviCat: is feature extensive, but they don't have real change scripts
(are drop/create).
- ModelRight: it's change script is not change at all (is just another
drop/create tool).
- TORA and other open source tools are really incomplete.
- TOAD is too confuse for simple day-by-day work.

Most of these tools or doesn't support PostgreSQL features (are too
generic), or doesn't do real forward engineer (are only able to drop/create
objects, not alter them), or cannot deal with partial diagrams (I can't deal
with only one diagram with hundred of tables at once).

Years ago I decided that the only way to do forward engineering was to
capture the changes I make to development databases in scripts and to
manually apply those scripts for release management.  This process
works and like you I've found the various commercial tools to have
various weaknesses.  So for forward engineering I say: quit using
tools and write scripts.


Yes, I've developed special tasks to update database automatically based 
on schema version. But this becomes a hard work very quick (because 
system grows too fast and we don't have dedicated DBA to deal with all 
those changes).




I'm also like you amazed how poor the various database diagramming
tools are -- they all suck.  Case Studio used to be pretty good back
in the day but I wouldn't recommend it today.  My personal take on
ERD/diagramming is that:

*) diagram generation should be automatic and useful


Yes - also, tool must have multi diagram support, in order we can 
organize different views of the structure.




*) human input to adjust the layout should not be required (every time
I move the stupid boxes and straighten the stupid lines I feel like
I'm finger painting)


Yes, I also hate that. DBWrench (tool I use today) has no auto layout of 
any kind, and I feel like a fool arranging tables when I'm supposed to 
do something that add value to our customers.




*) diagram tool should follow database changes and adjust the diagram
accordingly


Yes - database diff between database and model design with true change 
scripts. If I change the name of a column, I do expect Alter table XXX 
rename column  to OOO (or something like that, I can't remember the 
syntax right now).



*) diagram output should be standard html (only) without requiring
tool to log in and adjust diagram


I don't really care about output - SVG would be excellent, because it 
scales well and will print nicely.
I can tell that a data dictionary with diagramming output would be 
enough. If I can edit the data dictionary and system keep track of all 
changes, would be enough for me.
If PgAdmin would have the ability to maintain a data dictionary that is 
not the database itself, and plus the feature to synchronize with 
database (which would be any PostgreSQL database registered) would be 
optimal for me.




I've come around to the point of view that this is an unfilled niche
in the industry.  Furthermore, as long as scope is kept reasonably
down, this is not a very difficult project.  So I've decided (along
with Atri) to give it a shot.  Iniitial plans is to do plain html
dumps directly out of the database and use GraphVis to document
dependency flow.


That was my thought - if it's not too expensive, I can do something. 
That's my question regarding XML, would be very easy for me to work with 
it using Java (my preferred language, which I use on daily basis).
XML would work well with versioning systems, and is quite easy to diff 
etc.


If there is anything I can help, let me know.



merlin






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Atri Sharma]

On Fri, Dec 14, 2012 at 9:47 PM, Edson Richter edsonrich...@hotmail.comwrote:

 Em 14/12/2012 12:21, Merlin Moncure escreveu:

  On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com
 wrote:

 Em 13/12/2012 20:10, Merlin Moncure escreveu:

  On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter 
 edsonrich...@hotmail.com
 wrote:

 Has anyone created a XML Schema that would represent PostgreSQL
 database
 with all (or at least, major) structures?

 no -- furthermore, why would you want to?  what would be the consumer
 of this 'schema'?

 merlin


  I was wondering to create a tool for diagramming and database forward
 engineering.

 There are already few tools around.

 If you know a good diagramming tool able to database diff and forward
 engineering (with ALTER ..., not DROP and CREATE), I would like to
 know
 (by today I do use one commercial tool that is feature incomplete:
 DbWrench).

 Among others, I've considered also:
 - Sybase PowerDesigner: too expensive, does not support PostgreSQL
 9.1/9.2,
 so is not appropriate.
 - ERWin: too expensive, and doesn't have proper support for PostgreSQL
 9.1/9.2.
 - NaviCat: is feature extensive, but they don't have real change scripts
 (are drop/create).
 - ModelRight: it's change script is not change at all (is just another
 drop/create tool).
 - TORA and other open source tools are really incomplete.
 - TOAD is too confuse for simple day-by-day work.

 Most of these tools or doesn't support PostgreSQL features (are too
 generic), or doesn't do real forward engineer (are only able to
 drop/create
 objects, not alter them), or cannot deal with partial diagrams (I can't
 deal
 with only one diagram with hundred of tables at once).

 Years ago I decided that the only way to do forward engineering was to
 capture the changes I make to development databases in scripts and to
 manually apply those scripts for release management.  This process
 works and like you I've found the various commercial tools to have
 various weaknesses.  So for forward engineering I say: quit using
 tools and write scripts.


 Yes, I've developed special tasks to update database automatically based
 on schema version. But this becomes a hard work very quick (because system
 grows too fast and we don't have dedicated DBA to deal with all those
 changes).



 I'm also like you amazed how poor the various database diagramming
 tools are -- they all suck.  Case Studio used to be pretty good back
 in the day but I wouldn't recommend it today.  My personal take on
 ERD/diagramming is that:

 *) diagram generation should be automatic and useful


 Yes - also, tool must have multi diagram support, in order we can organize
 different views of the structure.



 *) human input to adjust the layout should not be required (every time
 I move the stupid boxes and straighten the stupid lines I feel like
 I'm finger painting)


 Yes, I also hate that. DBWrench (tool I use today) has no auto layout of
 any kind, and I feel like a fool arranging tables when I'm supposed to do
 something that add value to our customers.



 *) diagram tool should follow database changes and adjust the diagram
 accordingly


 Yes - database diff between database and model design with true change
 scripts. If I change the name of a column, I do expect Alter table XXX
 rename column  to OOO (or something like that, I can't remember the
 syntax right now).


  *) diagram output should be standard html (only) without requiring
 tool to log in and adjust diagram


 I don't really care about output - SVG would be excellent, because it
 scales well and will print nicely.
 I can tell that a data dictionary with diagramming output would be enough.
 If I can edit the data dictionary and system keep track of all changes,
 would be enough for me.
 If PgAdmin would have the ability to maintain a data dictionary that is
 not the database itself, and plus the feature to synchronize with
 database (which would be any PostgreSQL database registered) would be
 optimal for me.



 I've come around to the point of view that this is an unfilled niche
 in the industry.  Furthermore, as long as scope is kept reasonably
 down, this is not a very difficult project.  So I've decided (along
 with Atri) to give it a shot.  Iniitial plans is to do plain html
 dumps directly out of the database and use GraphVis to document
 dependency flow.


 That was my thought - if it's not too expensive, I can do something.
 That's my question regarding XML, would be very easy for me to work with it
 using Java (my preferred language, which I use on daily basis).
 XML would work well with versioning systems, and is quite easy to diff
 etc.

 If there is anything I can help, let me know.


Hi,

At the moment, the project is in prototyping phase. We are planning to dump
HTML from the database. Once it is ready for testing, we will let you know
how you can help(hopefully with testing!)

Regards,

Atri


 merlin





-- 
Sent via pgsql-general mailing list 

Re: [GENERAL] XML Schema for PostgreSQL database

[Merlin Moncure]

On Fri, Dec 14, 2012 at 10:17 AM, Edson Richter
edsonrich...@hotmail.com wrote:
 Em 14/12/2012 12:21, Merlin Moncure escreveu:

 On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com
 wrote:

 Em 13/12/2012 20:10, Merlin Moncure escreveu:

 On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter
 edsonrich...@hotmail.com
 wrote:

 Has anyone created a XML Schema that would represent PostgreSQL
 database
 with all (or at least, major) structures?

 no -- furthermore, why would you want to?  what would be the consumer
 of this 'schema'?

 merlin


 I was wondering to create a tool for diagramming and database forward
 engineering.

 There are already few tools around.

 If you know a good diagramming tool able to database diff and forward
 engineering (with ALTER ..., not DROP and CREATE), I would like to
 know
 (by today I do use one commercial tool that is feature incomplete:
 DbWrench).

 Among others, I've considered also:
 - Sybase PowerDesigner: too expensive, does not support PostgreSQL
 9.1/9.2,
 so is not appropriate.
 - ERWin: too expensive, and doesn't have proper support for PostgreSQL
 9.1/9.2.
 - NaviCat: is feature extensive, but they don't have real change scripts
 (are drop/create).
 - ModelRight: it's change script is not change at all (is just another
 drop/create tool).
 - TORA and other open source tools are really incomplete.
 - TOAD is too confuse for simple day-by-day work.

 Most of these tools or doesn't support PostgreSQL features (are too
 generic), or doesn't do real forward engineer (are only able to
 drop/create
 objects, not alter them), or cannot deal with partial diagrams (I can't
 deal
 with only one diagram with hundred of tables at once).

 Years ago I decided that the only way to do forward engineering was to
 capture the changes I make to development databases in scripts and to
 manually apply those scripts for release management.  This process
 works and like you I've found the various commercial tools to have
 various weaknesses.  So for forward engineering I say: quit using
 tools and write scripts.


 Yes, I've developed special tasks to update database automatically based on
 schema version. But this becomes a hard work very quick (because system
 grows too fast and we don't have dedicated DBA to deal with all those
 changes).



 I'm also like you amazed how poor the various database diagramming
 tools are -- they all suck.  Case Studio used to be pretty good back
 in the day but I wouldn't recommend it today.  My personal take on
 ERD/diagramming is that:

 *) diagram generation should be automatic and useful

hrm, I just found schemaspy. It looks pretty nice.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Has anyone created a XML Schema that would represent PostgreSQL database 
with all (or at least, major) structures?


Thanks,

Edson



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Merlin Moncure]

On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com wrote:
 Has anyone created a XML Schema that would represent PostgreSQL database
 with all (or at least, major) structures?

no -- furthermore, why would you want to?  what would be the consumer
of this 'schema'?

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Em 13/12/2012 20:10, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com wrote:

Has anyone created a XML Schema that would represent PostgreSQL database
with all (or at least, major) structures?

no -- furthermore, why would you want to?  what would be the consumer
of this 'schema'?

merlin




I was wondering to create a tool for diagramming and database forward 
engineering.


There are already few tools around.

If you know a good diagramming tool able to database diff and forward 
engineering (with ALTER ..., not DROP and CREATE), I would like to 
know (by today I do use one commercial tool that is feature incomplete: 
DbWrench).


Among others, I've considered also:
- Sybase PowerDesigner: too expensive, does not support PostgreSQL 
9.1/9.2, so is not appropriate.
- ERWin: too expensive, and doesn't have proper support for PostgreSQL 
9.1/9.2.
- NaviCat: is feature extensive, but they don't have real change scripts 
(are drop/create).
- ModelRight: it's change script is not change at all (is just another 
drop/create tool).

- TORA and other open source tools are really incomplete.
- TOAD is too confuse for simple day-by-day work.

Most of these tools or doesn't support PostgreSQL features (are too 
generic), or doesn't do real forward engineer (are only able to 
drop/create objects, not alter them), or cannot deal with partial 
diagrams (I can't deal with only one diagram with hundred of tables at 
once).


Thanks for your help,

Edson




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML indexing

[antony . carvalho]

Hi all,
Im trying to use the xpath funtion to get xml text nodes with efficiency,
than i created an index like :=#create  index doc_idx on testxml using
btree (((xpath('/book/id/text()', doc))[1]::text));But the index is used
just when i want xpath('/book/id/text()', doc). I want to get any text node
from xml doc without having to index that path every time i query.Do anyone
knows how to index like that ? Tx

[GENERAL] xml performance

[Raju Angani]

Hi,

I have been looking into Postgres for last 1 month. I was wondering if
there is a easy wy to store xml data in Postgres and not have any
performance impact.

Few properties of the XML doc that are stored in the table

1) Each doc could be 1mb in size.
2) Need to update few attributes with the document.
3) It could be 4 level nested document.
4) Insert/Select are the majority operations.

Could you please help me with any pointers, currently I'm using xDB, an EMC
product.

Thank you
Raju

[GENERAL] xml, xpath,postgres 9.1

[caracan]

Hello everybody, sorry for my english.
I need to extract the xml: lat, lon and ele.  The xml I have it save on a
table (gpx.object_value). For the first two works for me:

SELECT (xpath ('//lat ', gpx.object_value)) [i] FROM gpx
SELECT (xpath ('//lon ', gpx.object_value)) [i] FROM gpx

My question is how do ele ? Probe of everything:
'./trk/trkseg/trkpt/ele/text()'
'//ele'
'//ele/text()'
'//*[//ele]'

Please,help me.
TKANKS

Information:
postgres(postgis),eclipse,jsp

INSERT INTO gpx(object_name, object_value) VALUES ('t1.gpx',
cast(pg_read_file('t1.gpx', 0, 100) As xml));

Document xml:
- gpx xmlns=http://www.topografix.com/GPX/1/1; creator=MapMyTracks
version=1.1 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
xsi:schemaLocation=http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd;
- trk
- trkseg
- trkpt lat=12.645648333 lon=-7.884185
ele20.2/ele
time2007-12-30T08:27:03Z/time
/trkpt
- trkpt lat ...

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/xml-xpath-postgres-9-1-tp5164387p5164387.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Encoding problem

[Peter Eisentraut]

On mån, 2011-02-07 at 12:44 +0100, rsmogura wrote:
  I have test database with UTF-8 encoding. I putted there XML 
  aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to 
  iso8859-2, as the result of select I got
  ERROR: character 0xd081 of encoding UTF8 has no equivalent in 
  LATIN2
  Stan SQL:22P05.
 
  I should got result with characters entities for unparsable characters 
  #...;.

Hehe, interesting idea, but it's not implemented that way.  We don't
alter the XML data, except for the XML declaration.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Encoding problem

[Radosław Smogura]

I may write some patch, actually text mode will not be affected, becuase it's 
text mode, and patch will fail if client encoding is reacher then server 
(one possiblity in this situation is to XML-encode to client encoding, text-
rencode to server encoding)

But looking at code same thing could occur with binary recv. I saw there text 
based XML conversion (it's altering XML in some way). According to doc I can 
store XML in any encodign using binary mode.

I think if text conversion fails, then XML rewrite should occur, and all 
unparsable character should be converted to XML entities...

Actually it's XML, not varchar with parsing :)

Peter Eisentraut pete...@gmx.net Wednesday 09 February 2011 23:29:29
 On mån, 2011-02-07 at 12:44 +0100, rsmogura wrote:
   I have test database with UTF-8 encoding. I putted there XML
   aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to
   iso8859-2, as the result of select I got
   ERROR: character 0xd081 of encoding UTF8 has no equivalent in
   LATIN2
   Stan SQL:22P05.
   
   I should got result with characters entities for unparsable characters
   #...;.
 
 Hehe, interesting idea, but it's not implemented that way.  We don't
 alter the XML data, except for the XML declaration.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML Encoding problem

[rsmogura]

Hi,

I have test database with UTF-8 encoding. I putted there XML 
aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to 
iso8859-2, as the result of select I got
ERROR: character 0xd081 of encoding UTF8 has no equivalent in 
LATIN2

Stan SQL:22P05.

I should got result with characters entities for unparsable characters 
#...;.


Kind regards,
Radosław Smogura

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - DOCTYPE element - documentation suggestion

[Peter Eisentraut]

On fre, 2010-06-18 at 02:43 +0800, Craig Ringer wrote:
 The xml datatype documentation should probably mention that whole
 documents must be loaded with an XMLPARSE(DOCUMENT 'doc_text_here),
 they
 cannot just be cast from text to xml as happens when you pass an xml
 document as text to a parameter during an INSERT. This should probably
 appear under CREATING XML VALUES in:
 
   http://www.postgresql.org/docs/current/static/datatype-xml.html
 
 ... and probably deserves mention in a new CAVEATS or NOTES
 section
 too, as it' *will* catch people out even if they R TFM.

Done


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML - DOCTYPE element - documentation suggestion

[Craig Ringer]

Hi all

I've been working with XML storage in Pg and was puzzled by the fact
that Pg appears to refuse to store a document with a DOCTYPE declaration
- it was interpreting it as a regular element and rejecting it.

This turns out to be because Pg parses XML as a fragment (ie option
CONTENT) when casting, and XML fragments cannot have a doctype.
Unfortunately the error is ... unhelpful ... and the documentation
neglects to mention this issue. Hence my post.

I didn't see anything about this in the FAQ or in the docs for the XML
datatype
(http://www.postgresql.org/docs/current/interactive/datatype-xml.html)
and was half-way through writing this post when I found a helpful
message on the list:

 http://www.mail-archive.com/pgsql-general@postgresql.org/msg119713.html

that hinted the way. Even then it took me a while to figure out that you
can't specify DOCUMENT or CONTENT on the XML type its self, but must
specify it while parsing instead and use a CHECK constraint if you want
to require storage of whole documents in a field.

The xml datatype documentation should probably mention that whole
documents must be loaded with an XMLPARSE(DOCUMENT 'doc_text_here), they
cannot just be cast from text to xml as happens when you pass an xml
document as text to a parameter during an INSERT. This should probably
appear under CREATING XML VALUES in:

  http://www.postgresql.org/docs/current/static/datatype-xml.html

... and probably deserves mention in a new CAVEATS or NOTES section
too, as it' *will* catch people out even if they R TFM.



I'd expect this to work:

CREATE TABLE test_xml ( doc xml );

INSERT INTO test_xml ( doc ) VALUES (
$$?xml version=1.0 encoding=utf-8?
!DOCTYPE test SYSTEM 'test.dtd'testdummy content/test$$
);

... but it fails with:


ERROR:  invalid XML content
LINE 2: $$?xml version=1.0 encoding=utf-8?
^
DETAIL:  Entity: line 2: parser error : StartTag: invalid element name
!DOCTYPE test SYSTEM 'test.dtd'testdummy content/test
 ^


though xmllint (from libxml) is quite happy with the document. This had
me quite confused for a while.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML index

[Chris Roffler]

I have a table with an xml column, created an index as follows:

*CREATE INDEX xml_index*
*  ON test*
*  USING btree*
*  (((xpath('//*/ChangedBy/text()'::text, external_attributes))[1]::text));*

And here is my select statement:

*Select uuid from t *
*  where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
'User';*
*
*
I then insert 100rows into this table, then do a select with the above
statement.
*Explain *shows that the query is using the xml_index.

Now I insert 5000 more rows and *Explain *shows that the query does not use
the xml_index anymore.
However, if I drop the index and re create it, then *Explain *tells me that
it's using the index again.

Any ideas what is going on here ?

Thanks
Chris
*
*

Re: [GENERAL] XML index

[Thom Brown]

On 27 May 2010 12:22, Chris Roffler croff...@earthlink.net wrote:
 I have a table with an xml column, created an index as follows:
 CREATE INDEX xml_index
   ON test
   USING btree
   (((xpath('//*/ChangedBy/text()'::text, external_attributes))[1]::text));
 And here is my select statement:
 Select uuid from t
   where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
 'User';
 I then insert 100rows into this table, then do a select with the above
 statement.
 Explain shows that the query is using the xml_index.
 Now I insert 5000 more rows and Explain shows that the query does not use
 the xml_index anymore.
 However, if I drop the index and re create it, then Explain tells me that
 it's using the index again.
 Any ideas what is going on here ?
 Thanks
 Chris


I'd run an ANALYZE after inserting 5000 more rows.  The stats will be
out of date.

Thom

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML index

[Chris Roffler]

Tried that  same thing

On Thu, May 27, 2010 at 1:53 PM, Thom Brown thombr...@gmail.com wrote:

 On 27 May 2010 12:22, Chris Roffler croff...@earthlink.net wrote:
  I have a table with an xml column, created an index as follows:
  CREATE INDEX xml_index
ON test
USING btree
(((xpath('//*/ChangedBy/text()'::text,
 external_attributes))[1]::text));
  And here is my select statement:
  Select uuid from t
where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
  'User';
  I then insert 100rows into this table, then do a select with the above
  statement.
  Explain shows that the query is using the xml_index.
  Now I insert 5000 more rows and Explain shows that the query does not use
  the xml_index anymore.
  However, if I drop the index and re create it, then Explain tells me that
  it's using the index again.
  Any ideas what is going on here ?
  Thanks
  Chris
 

 I'd run an ANALYZE after inserting 5000 more rows.  The stats will be
 out of date.

 Thom

Re: [GENERAL] XML index

[Chris Roffler]

Changed the create index statement to :   USING hash  and it seems to work.

Any idea why btree does not work ?

Thanks
Chris

On Thu, May 27, 2010 at 3:47 PM, Chris Roffler croff...@earthlink.netwrote:

 Tried that  same thing


 On Thu, May 27, 2010 at 1:53 PM, Thom Brown thombr...@gmail.com wrote:

 On 27 May 2010 12:22, Chris Roffler croff...@earthlink.net wrote:
  I have a table with an xml column, created an index as follows:
  CREATE INDEX xml_index
ON test
USING btree
(((xpath('//*/ChangedBy/text()'::text,
 external_attributes))[1]::text));
  And here is my select statement:
  Select uuid from t
where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
  'User';
  I then insert 100rows into this table, then do a select with the above
  statement.
  Explain shows that the query is using the xml_index.
  Now I insert 5000 more rows and Explain shows that the query does not
 use
  the xml_index anymore.
  However, if I drop the index and re create it, then Explain tells me
 that
  it's using the index again.
  Any ideas what is going on here ?
  Thanks
  Chris
 

 I'd run an ANALYZE after inserting 5000 more rows.  The stats will be
 out of date.

 Thom

Re: [GENERAL] XML Index again

[Chris Roffler]

Alban

Thanks for your help, your suggestion worked.

I need another xpath expression to find any Attribute with  Name =
under the Attributes node. (not just if it is in a specific position)
see   query below.
How do I create an index for this xpath expression ?

Thanks
Chris


 SELECT * FROM time_series
WHERE
array_upper((xpath('/AttributeList/Attributes/Attribute[Name=x]',
external_attributes)),1)  0


On Sun, Mar 7, 2010 at 1:11 PM, Alban Hertroys 
dal...@solfertje.student.utwente.nl wrote:

 On 7 Mar 2010, at 11:02, Chris Roffler wrote:

  I still have some problems with my xml index
 
  CREATE INDEX xml_index
ON time_series
USING btree
((
(xpath('/AttributeList/Attributes/Attribute/Name/text()',
 external_attributes))[1]::text));
 
  When I run the following query the index is not used :
 
  select id from time_series where
  array_upper(
  (xpath('/AttributeList/Attributes/Attribute[Name=Attribute122021]',
 external_attributes))
  , 1)  0
 
  Any Idea on how to configure the index ?

 There are a couple of cases where Postgres won't use your index, but in
 this case it's quite clearly because you're asking for (quite) a different
 expression than the one you indexed.

 You seem to want to test for the existence of nodes with a specific name,
 maybe this is what you're looking for?:

 SELECT id FROM time_series t1 WHERE EXISTS (
SELECT 1
  FROM time_series t2
 WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()',
 external_attributes))[1]::text = ('Attribute122021', external_attributes)
   AND t2.id = t1.id
 );

 It's just a guess at what you're trying to do, so I may very well have
 gotten it wrong. The important part is that you need to use the expression
 you indexed in your where clause, or the database has no idea you mean
 something similar as to what you indexed.

 Alban Hertroys

 --
 If you can't see the forest for the trees,
 cut the trees and you'll see there is no forest.


 !DSPAM:1034,4b9389d6296921789322580!

Re: [GENERAL] XML Index again

[Alban Hertroys]

On 8 Mar 2010, at 11:39, Chris Roffler wrote:

 Alban
 
 Thanks for your help, your suggestion worked.
 
 I need another xpath expression to find any Attribute with  Name = 
 under the Attributes node. (not just if it is in a specific position)
 see   query below.

Your previous query wasn't about attributes in any specific position - it 
returned documents that contained more than zero attributes matching a given 
name. What are you trying to do this time?

 How do I create an index for this xpath expression ?

You don't need to create another index (although one w/o the 
external_attributes column would probably be more convenient); the index you 
have contains those names already.

Just make sure you use the same expression you used to create the index to 
match the part in your xml and compare it to the text you're looking for.

If you want to use indexes on your xml, then you'll need to stop putting the 
variable parts of your queries inside your xpath expressions - you make them 
unindexable that way. So move those [Name='']'s out of your xpath 
expressions. Instead have the expressions result in the names so that you can 
compare them to the names stored in your index(es).

It won't be as fast as looking for those names using xpath in an xml document, 
as every attribute name is a candidate for comparison now, but at least it's 
indexable.

Alternatively you could try to build an index from the names contained in each 
xml document. Something like:

CREATE INDEX xml_attribute_names
ON time_series
 USING btree (xpath('/AttributeList/Attributes/Attribute/Name/text()'));

This stores the array of all attribute names in an index. You can query for 
existence of specific attributes using the ANY operator on the resulting array 
(see archives and docs for how to do that).

I believe (I've never needed to use arrays) the syntax is:

SELECT * FROM time_series WHERE '' = ANY 
(xpath('/AttributeList/Attributes/Attribute/text()'));

It'll probably be faster than the previous solution, but use more disk space 
and memory.

  SELECT * FROM time_series 
 WHERE 
 array_upper((xpath('/AttributeList/Attributes/Attribute[Name=x]',   
 external_attributes)),1)  0  
 
 
 On Sun, Mar 7, 2010 at 1:11 PM, Alban Hertroys 
 dal...@solfertje.student.utwente.nl wrote:
...
 You seem to want to test for the existence of nodes with a specific name, 
 maybe this is what you're looking for?:
 
 SELECT id FROM time_series t1 WHERE EXISTS (
SELECT 1
  FROM time_series t2
 WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()', 
 external_attributes))[1]::text = ('Attribute122021', external_attributes)
   AND t2.id = t1.id
 );

For clarity, if you would have an index on just that xpath expression - without 
the external_attributes column - this query would look simpler:

SELECT id FROM time_series t1 WHERE EXISTS (
   SELECT 1
 FROM time_series t2
WHERE xpath('/AttributeList/Attributes/Attribute/Name/text()')[1]::text 
= 'Attribute122021'
  AND t2.id = t1.id
);

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b94df40296929445119198!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Index again

[Alban Hertroys]

On 8 Mar 2010, at 11:39, Chris Roffler wrote:

 Alban
 
 Thanks for your help, your suggestion worked.
 
 I need another xpath expression to find any Attribute with  Name = 
 under the Attributes node. (not just if it is in a specific position)
 see   query below.
 How do I create an index for this xpath expression ?

Come to think of it... You seem to be storing an attribute/value list in the 
database in XML. Is there any reason not to store the attributes and their 
values as a normal table? Your approach seems a bit over-complicated and you 
seem to have some trouble getting your own mind around it.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b94e083296929244758856!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Index again

[Chris Roffler]

Alban

thanks for your response. I understand what you are saying .

Your previous query wasn't about attributes in any specific position - it
returned documents that contained more than zero attributes matching a
given name. What are you trying to do this time?

And that is exactly my problem, only if the first attribute in the
Attributes is a match the query returns successful.
Please see my example below

Thanks
Chris


CREATE INDEX xml_index
  ON time_series
  USING btree
  ((
  (xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text));


SELECT id FROM time_series t1 WHERE EXISTS (
SELECT 1
  FROM time_series t2
 WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text = 'x'
   AND t2.id = t1.id
 );

When I run the select it uses the index.

The first query I run is with 'x' = 'Attribute102021' this works like a
champ.
The second query is run with '' = 'Attribute202021' and returns null. (
this is the second Attribute )


 XML example

AttributeList
Attributes
Attribute
NameAttribute102021/Name
Value1/Value
LastChanged2010-03-05T05:44:36.796-05:00/LastChanged
ChangedByChris/ChangedBy
/Attribute
Attribute
NameAttribute202021/Name
Value2/Value
LastChanged2010-03-05T05:44:36.796-05:00/LastChanged
ChangedByMaya/ChangedBy
/Attribute
/Attributes
/AttributeList

On Mon, Mar 8, 2010 at 1:27 PM, Alban Hertroys 
dal...@solfertje.student.utwente.nl wrote:

 On 8 Mar 2010, at 11:39, Chris Roffler wrote:

  Alban
 
  Thanks for your help, your suggestion worked.
 
  I need another xpath expression to find any Attribute with  Name =
 under the Attributes node. (not just if it is in a specific position)
  see   query below.

 Your previous query wasn't about attributes in any specific position - it
 returned documents that contained more than zero attributes matching a given
 name. What are you trying to do this time?

  How do I create an index for this xpath expression ?

 You don't need to create another index (although one w/o the
 external_attributes column would probably be more convenient); the index you
 have contains those names already.

 Just make sure you use the same expression you used to create the index to
 match the part in your xml and compare it to the text you're looking for.

 If you want to use indexes on your xml, then you'll need to stop putting
 the variable parts of your queries inside your xpath expressions - you make
 them unindexable that way. So move those [Name='']'s out of your xpath
 expressions. Instead have the expressions result in the names so that you
 can compare them to the names stored in your index(es).

 It won't be as fast as looking for those names using xpath in an xml
 document, as every attribute name is a candidate for comparison now, but at
 least it's indexable.

 Alternatively you could try to build an index from the names contained in
 each xml document. Something like:

 CREATE INDEX xml_attribute_names
ON time_series
  USING btree (xpath('/AttributeList/Attributes/Attribute/Name/text()'));

 This stores the array of all attribute names in an index. You can query for
 existence of specific attributes using the ANY operator on the resulting
 array (see archives and docs for how to do that).

 I believe (I've never needed to use arrays) the syntax is:

 SELECT * FROM time_series WHERE '' = ANY
 (xpath('/AttributeList/Attributes/Attribute/text()'));

 It'll probably be faster than the previous solution, but use more disk
 space and memory.

   SELECT * FROM time_series
  WHERE
 array_upper((xpath('/AttributeList/Attributes/Attribute[Name=x]',
 external_attributes)),1)  0
 
 
  On Sun, Mar 7, 2010 at 1:11 PM, Alban Hertroys 
 dal...@solfertje.student.utwente.nl wrote:
 ...
  You seem to want to test for the existence of nodes with a specific name,
 maybe this is what you're looking for?:
 
  SELECT id FROM time_series t1 WHERE EXISTS (
 SELECT 1
   FROM time_series t2
  WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()',
 external_attributes))[1]::text = ('Attribute122021', external_attributes)
AND t2.id = t1.id
  );

 For clarity, if you would have an index on just that xpath expression -
 without the external_attributes column - this query would look simpler:

 SELECT id FROM time_series t1 WHERE EXISTS (
   SELECT 1
 FROM time_series t2
 WHERE
 xpath('/AttributeList/Attributes/Attribute/Name/text()')[1]::text =
 'Attribute122021'
   AND t2.id = t1.id
 );

 Alban Hertroys

 --
 If you can't see the forest for the trees,
 cut the trees and you'll see there is no forest.


 !DSPAM:1034,4b94df38296921956520267!

Re: [GENERAL] XML Index again

[Alban Hertroys]

On 8 Mar 2010, at 13:23, Chris Roffler wrote:

 Alban
 
 thanks for your response. I understand what you are saying .
 
 Your previous query wasn't about attributes in any specific position - it 
 returned documents that contained more than zero attributes matching a 
 given name. What are you trying to do this time?
 
 And that is exactly my problem, only if the first attribute in the Attributes 
 is a match the query returns successful.
 Please see my example below 

Ah yes it would, as you only index the first attribute. I guess you'll have to 
give indexing the list of attributes a try as I outlined in a previous message.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4b94ef13296926894712957!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Index again

[Chris Roffler]

Yup you are right  however I am trying to run benchmarks with the two
solutions.

The xml solution will give us more flexibility in the future , just in case
we do not have attribute/value lists :)


On Mon, Mar 8, 2010 at 1:33 PM, Alban Hertroys 
dal...@solfertje.student.utwente.nl wrote:

 On 8 Mar 2010, at 11:39, Chris Roffler wrote:

  Alban
 
  Thanks for your help, your suggestion worked.
 
  I need another xpath expression to find any Attribute with  Name =
 under the Attributes node. (not just if it is in a specific position)
  see   query below.
  How do I create an index for this xpath expression ?

 Come to think of it... You seem to be storing an attribute/value list in
 the database in XML. Is there any reason not to store the attributes and
 their values as a normal table? Your approach seems a bit over-complicated
 and you seem to have some trouble getting your own mind around it.

 Alban Hertroys

 --
 If you can't see the forest for the trees,
 cut the trees and you'll see there is no forest.


 !DSPAM:1034,4b94e081296926114815748!

[GENERAL] XML Index again

[Chris Roffler]

I still have some problems with my xml index

CREATE INDEX xml_index
  ON time_series
  USING btree
  ((
  (xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text));

When I run the following query the index is not used :

select id from time_series where
array_upper(
(xpath('/AttributeList/Attributes/Attribute[Name=Attribute122021]',
external_attributes))
, 1)  0

Any Idea on how to configure the index ?

Thanks
Chris

Re: [GENERAL] XML Index again

[Alban Hertroys]

On 7 Mar 2010, at 11:02, Chris Roffler wrote:

 I still have some problems with my xml index
 
 CREATE INDEX xml_index
   ON time_series
   USING btree
   ((
   (xpath('/AttributeList/Attributes/Attribute/Name/text()', 
 external_attributes))[1]::text));
 
 When I run the following query the index is not used :
 
 select id from time_series where  
 array_upper(
 (xpath('/AttributeList/Attributes/Attribute[Name=Attribute122021]', 
 external_attributes))
 , 1)  0
 
 Any Idea on how to configure the index ?

There are a couple of cases where Postgres won't use your index, but in this 
case it's quite clearly because you're asking for (quite) a different 
expression than the one you indexed.

You seem to want to test for the existence of nodes with a specific name, maybe 
this is what you're looking for?:

SELECT id FROM time_series t1 WHERE EXISTS (
SELECT 1
  FROM time_series t2
 WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()', 
external_attributes))[1]::text = ('Attribute122021', external_attributes)
   AND t2.id = t1.id
);

It's just a guess at what you're trying to do, so I may very well have gotten 
it wrong. The important part is that you need to use the expression you indexed 
in your where clause, or the database has no idea you mean something similar as 
to what you indexed.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b9389db296924445911763!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Index again

[Chris]

Alban

thanks for your replay. Yes I am looking for node exists ...

I'll give it a roll.



There are a couple of cases where Postgres won't use your index, but in this 
case it's quite clearly because you're asking for (quite) a different 
expression than the one you indexed.

You seem to want to test for the existence of nodes with a specific name, 
maybe this is what you're looking for?:

SELECT id FROM time_series t1 WHERE EXISTS (
   SELECT 1
 FROM time_series t2
WHERE (xpath('/AttributeList/Attributes/Attribute/Name/text()', 
 external_attributes))[1]::text = ('Attribute122021', external_attributes)
  AND t2.id = t1.id
);

It's just a guess at what you're trying to do, so I may very well have gotten 
it wrong. The important part is that you need to use the expression you 
indexed in your where clause, or the database has no idea you mean something 
similar as to what you indexed.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b9389db296924445911763!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML performance tuning

[Bruce Momjian]

Chris Roffler wrote:
 Are there any guidelines for XML performance tuning ?

Uh, no, I have never seen any.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML performance tuning

[Chris Roffler]

Are there any guidelines for XML performance tuning ?

Thanks
Chris

Re: [GENERAL] XML Type validates against xml schema?

[Peter Eisentraut]

On tis, 2010-01-05 at 16:06 -0800, Andrew Lardinois wrote:
 Poking around in the 8.5 Devel Documentation section 8.13.1, the XML
 Type, I noticed that:
 
  The xml type does not validate input values against a document type
 declaration (DTD), even when the input value specifies a DTD
 
 I suppose the same is true in the case that the XML should validate
 against an (xml) schema?

The xml type does not do XML Schema validation.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML Type validates against xml schema?

[Andrew Lardinois]

Poking around in the 8.5 Devel Documentation section 8.13.1, the XML Type, I
noticed that:

 The xml type does not validate input values against a document type
declaration (DTD), even when the input value specifies a DTD

I suppose the same is true in the case that the XML should validate against
an (xml) schema?

thanks,
Andrew Lardinois

Re: [GENERAL] xml import/export tools and performance

[Brian Modra]

2009/11/6 Ivan Sergio Borgonovo m...@webthatworks.it:
 I need to import/export through xml files.

 I was looking for tools/examples/HOWTO for postgresql.

 Right now I still don't know the details of the requirements.

 I know I won't need a GUI.

 I know one of the exporting parties will be a MS SQL 2005 server, so
 it would be nice if there was an easy way to import xml generated
 with the FOR XML AUTO, XMLSCHEMA ('forpg').

 I'd like to have a tool that can write XSD from queries
 automatically.

 I may need to strictly specify one of the xml output format since
 one of the importing parties pretend to support xml with something
 that is more like a tagged csv.

 Currently I'm mostly looking around to see where it would be
 convenient to move the boundaries of the problem.

 I'd be curious about what kind of performance impact they may have
 compared to COPY.

 thanks

I don't know of any such tools, but they may exist, if not:

Tell me the XML format you want in/out of postgres, and I can write a
tool for you. I had a quick look just now to see what the MS Access
format is, and its typically difficult to wade through the moutains of
useless information that people have posted on teh web about Microsoft
products, so i lost interest. However, if you can point me in the
right direction, so I can see the DTD or examples, or both... then I
can tell you how easy/difficult it would be to write a converter.

 --
 Ivan Sergio Borgonovo
 http://www.webthatworks.it


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml import/export tools and performance

[John R Pierce]

Ivan Sergio Borgonovo wrote:

I know one of the exporting parties will be a MS SQL 2005 server, so
it would be nice if there was an easy way to import xml generated
with the FOR XML AUTO, XMLSCHEMA ('forpg').
  


Microsoft SQL Server has a pretty good data translation tool, it used to 
be called DTS, but I think its called something else now... if you 
install a ODBC or OLEDB Postgres driver ontp the SQL Server system, and 
configure your postgres server to allow the MS SQL Server to be able to 
connect to it, you can use this service to bulk transfer data  between 
Postgres and MS SQL Server, either full tables or specific queries.


ahh, its called SSIS now, here's a pretty good summary of it,
http://en.wikipedia.org/wiki/SQL_Server_Integration_Services

and some info on MS's webpile about it...
http://www.microsoft.com/sqlserver/2005/en/us/integration-services.aspx
http://technet.microsoft.com/en-us/library/cc917721.aspx

if you go this route, you don't have to mess with any sort of 
import/export files, XML or otherwise, just move the data directly 
between the databases over the network.



personally, I find XML is extremely inefficient.  the idea of sending 
the name of every field with each data record just seems like a bad 
idea.  you end up with more metadata than actual data.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] xml import/export tools and performance

[Ivan Sergio Borgonovo]

I need to import/export through xml files.

I was looking for tools/examples/HOWTO for postgresql.

Right now I still don't know the details of the requirements.

I know I won't need a GUI.

I know one of the exporting parties will be a MS SQL 2005 server, so
it would be nice if there was an easy way to import xml generated
with the FOR XML AUTO, XMLSCHEMA ('forpg').

I'd like to have a tool that can write XSD from queries
automatically.

I may need to strictly specify one of the xml output format since
one of the importing parties pretend to support xml with something
that is more like a tagged csv.

Currently I'm mostly looking around to see where it would be
convenient to move the boundaries of the problem.

I'd be curious about what kind of performance impact they may have
compared to COPY.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to string, ascii x utf8 conversion

[Peter Eisentraut]

On Monday 20 July 2009 19:46:01 Radek Novotný wrote:
 query_to_xml('select Nazev as TITLE, Datum as DATE, Autor_Akce as meta
 rel=''action_author'', 

 gave me

 table xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
 row
   TITLETest/TITLE
   DATE2009-07-20/DATE
   meta_x0020_rel_x003D__x0027_action_author_x0027_test
 /meta_x0020_rel_x003D__x0027_action_author_x0027_

 How can i transcode this well generated xml(ascii) to string(UTF8) without
 the escape characters?

This has nothing to do with ASCII vs. UTF-8.  The problem is that meta
rel='action_author' is not a valid XML *element* name, so it needs to be 
escaped.  What you are trying to achieve is presumably to get a meta element 
with a rel attribute.  To get that, you will have to run the result of 
query_to_xml through some postprocessing (with XSLT, for example).

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] xml to string, ascii x utf8 conversion

[Radek Novotný]

Hello,
having problem with conversion while doing function query_to_xml.

query_to_xml('select Nazev as TITLE, Datum as DATE, Autor_Akce as meta 
rel=''action_author'', 

gave me

table xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
row
  TITLETest/TITLE
  DATE2009-07-20/DATE
  meta_x0020_rel_x003D__x0027_action_author_x0027_test 
/meta_x0020_rel_x003D__x0027_action_author_x0027_

How can i transcode this well generated xml(ascii) to string(UTF8) without the 
escape characters?

I found nothing on google...

Regards,
Radek Novotny


-- 

www.publicstream.cz - vytvořili jsme produkt pro živé přenosy (online 
streaming) 
a videoarchív ve formátu FLASH.

Bc. Radek Novotný
jednatel Mediawork group s.r.o.

tel.: +420 724 020 361
email: radek.novo...@mediawork.cz
http://www.mediawork.cz

Re: [GENERAL] XML import with DTD

[Roy Walter]

It's not an xpath problem it's an XML import problem. Sorry if I wasn't 
clear.


Consider the following example queries. This one works fine:

   INSERT INTO wms_collection (docxml) VALUES (XMLPARSE(content(
   '?xml version=1.0 encoding=ISO-8859-1?
   shop
   productShoes/product
   /shop')))

This one does not:

   INSERT INTO wms_collection (docxml) VALUES (XMLPARSE(content(
   '?xml version=1.0 encoding=ISO-8859-1?
   !DOCTYPE publicwhip
   [
   !ENTITY ndash   #8211;
   !ENTITY mdash   #8212;
   ]
   shop
   productShoes/product
   /shop')))

Both are valid XML but the second query fails as follows:

   ERROR:  invalid XML content
   DETAIL:  Entity: line 2: parser error : StartTag: invalid element name
   !DOCTYPE publicwhip
^
   Entity: line 4: parser error : StartTag: invalid element name
   !ENTITY ndash   #8211;
^
   Entity: line 5: parser error : StartTag: invalid element name
   !ENTITY mdash   #8212;

-- Roy


arta...@comcast.net wrote:

Post a snippet of the xml and xpath you are trying to use.


Scott

- Original Message -
From: Roy Walter w...@brookhouse.co.uk
To: pgsql-general@postgresql.org
Sent: Friday, July 10, 2009 7:49:00 AM GMT -08:00 US/Canada Pacific
Subject: [GENERAL] XML import with DTD

Hi

I'm trying to use the XPath functionality of Postgres.

I can populate a text field (unparsed) with XML data but as far as I 
can see the xpath() function [now] only works on the xml data type.


When I try to populate a text field with XML data containing a DTD, 
however, the parser chokes. If I strip the DTD the parser chokes on 
undefined entities which are defined in the DTD.


(I switched the app' to from MySQL to Postgres because while MySQL 
works it returns matches in undelimited form which is next to useless 
if, for example, you return multiple attributes from a node.)


Does anyone know of  a solution to this problem?

Windows 2000 Server
Postgres 8.4

Regards
Roy Walter



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.387 / Virus Database: 270.13.9/2229 - Release Date: 07/10/09 07:05:00


  

Re: [GENERAL] XML import with DTD

[Tom Lane]

Roy Walter w...@brookhouse.co.uk writes:
 This one does not:

 INSERT INTO wms_collection (docxml) VALUES (XMLPARSE(content(
 '?xml version=1.0 encoding=ISO-8859-1?
 !DOCTYPE publicwhip
 [
 !ENTITY ndash   #8211;
 !ENTITY mdash   #8212;
 ]
 shop
 productShoes/product
 /shop')))

What I know about XML wouldn't fill a thimble, but shouldn't you say
DOCUMENT not CONTENT if you are trying to provide a complete document?
Doing that seems to make this work without error.

The fine manual states near the bottom of 8.13.1
http://www.postgresql.org/docs/8.4/static/datatype-xml.html
that CONTENT is less restrictive than DOCUMENT, but at least for
this specific point that seems not to be true.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML import with DTD

[Roy Walter]

Doh! That's it. Thanks a million.

-- Roy

Tom Lane wrote:

Roy Walter w...@brookhouse.co.uk writes:
  

This one does not:



  

INSERT INTO wms_collection (docxml) VALUES (XMLPARSE(content(
'?xml version=1.0 encoding=ISO-8859-1?
!DOCTYPE publicwhip
[
!ENTITY ndash   #8211;
!ENTITY mdash   #8212;
]
shop
productShoes/product
/shop')))



What I know about XML wouldn't fill a thimble, but shouldn't you say
DOCUMENT not CONTENT if you are trying to provide a complete document?
Doing that seems to make this work without error.

The fine manual states near the bottom of 8.13.1
http://www.postgresql.org/docs/8.4/static/datatype-xml.html
that CONTENT is less restrictive than DOCUMENT, but at least for
this specific point that seems not to be true.

regards, tom lane



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.387 / Virus Database: 270.13.10/2231 - Release Date: 07/11/09 05:57:00


  

[GENERAL] XML import with DTD

[Roy Walter]

Hi

I'm trying to use the XPath functionality of Postgres.

I can populate a text field (unparsed) with XML data but as far as I can 
see the xpath() function [now] only works on the xml data type.


When I try to populate a text field with XML data containing a DTD, 
however, the parser chokes. If I strip the DTD the parser chokes on 
undefined entities which are defined in the DTD.


(I switched the app' to from MySQL to Postgres because while MySQL works 
it returns matches in undelimited form which is next to useless if, for 
example, you return multiple attributes from a node.)


Does anyone know of  a solution to this problem?

Windows 2000 Server
Postgres 8.4

Regards
Roy Walter

Re: [GENERAL] XML import with DTD

[artacus]

Post a snippet of the xml and xpath you are trying to use. 


Scott 

- Original Message - 
From: Roy Walter w...@brookhouse.co.uk 
To: pgsql-general@postgresql.org 
Sent: Friday, July 10, 2009 7:49:00 AM GMT -08:00 US/Canada Pacific 
Subject: [GENERAL] XML import with DTD 

Hi 

I'm trying to use the XPath functionality of Postgres. 

I can populate a text field (unparsed) with XML data but as far as I can see 
the xpath() function [now] only works on the xml data type. 

When I try to populate a text field with XML data containing a DTD, however, 
the parser chokes. If I strip the DTD the parser chokes on undefined entities 
which are defined in the DTD. 

(I switched the app' to from MySQL to Postgres because while MySQL works it 
returns matches in undelimited form which is next to useless if, for example, 
you return multiple attributes from a node.) 

Does anyone know of a solution to this problem? 

Windows 2000 Server 
Postgres 8.4 

Regards 
Roy Walter 

Re: [GENERAL] xml to table (as oppose to table to xml)

[Peter Eisentraut]

On Monday 01 June 2009 12:53:08 Grzegorz Jaśkiewicz wrote:
 is there any way currently to convert xml file in format like below,
 to a table ?

I have some code that does this, but it was written a long time ago and will 
probably need some polishing.

One main problem is how you specify that exact mapping mechanism.  AFAIR, my 
code accepted as input the exact format that table_to_xml etc. put out (and 
probably crashes otherwise).  In reality you probably want an XSLT 
transformation in between.  Which would require a smoother XSLT integration 
into the backend code.

I think this is something we could work on for 8.5.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Grzegorz Jaśkiewicz]

I can test/review the code, if you want. It would be a nice thing to
have in postgresql, obviously once you prepare statement enough to
convince Tom :)
XSLT is a necessity , otherwise it won't be standard, and thus - quite useless.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] xml to table (as oppose to table to xml)

[Grzegorz Jaśkiewicz]

is there any way currently to convert xml file in format like below,
to a table ?


foo
  section
ssc id=foo1
   foo_data a=1 b=2/
   foo_more_data c=a d=b/
/ssc
  /section

  section
ssc id=foo2
...
/ssc
  /section

  section
...
  /section

  section
...
  /section
/foo



-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Sam Mason]

On Mon, Jun 01, 2009 at 10:53:08AM +0100, Grzegorz Jaaakiewicz wrote:
 is there any way currently to convert xml file in format like below,
 to a table ?

I've had good luck with the xpath support in PG[1] and some variant of
the unnest function that's in PG 8.4 (various versions[2] have been
posted to this list for older versions).

-- 
  Sam  http://samason.me.uk/
 
 [1] 
http://www.postgresql.org/docs/current/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
 [2] 
http://archives.postgresql.org/message-id/b88c3460905290021o6870bb46tec88ced0c4064...@mail.gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Grzegorz Jaśkiewicz]

xpath is fine, but not when you have 10+ fields to extract ;)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Sam Mason]

On Mon, Jun 01, 2009 at 11:22:14AM +0100, Grzegorz Jaaakiewicz wrote:
 xpath is fine, but not when you have 10+ fields to extract ;)

I've got a few views pulling 10 to 15 values out of XML files and
it works OK, not amazing performance but for what I'm doing it's no
problem.  Scaling beyond that would seem to start getting somewhat
unmaintainable, but it seems to be working out that the functional
dependencies put tight constraints on what I can put into a table and
hence the number of values I pull out in one go.

Worst case you could always generate the SQL from other code, or even do
the parsing from XML to something more structured outside PG.

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Pavel Stehule]

Hello

you can use simple perl parser

an sample is on
http://www.postgres.cz/index.php/PL/Perlu_-_Untrusted_Perl#Generov.C3.A1n.C3.AD.2C_zpracov.C3.A1n.C3.AD_XML

code is in english and perl, description in czech, sorry

regards
Pavel Stehule

2009/6/1 Grzegorz Jaśkiewicz gryz...@gmail.com:
 xpath is fine, but not when you have 10+ fields to extract ;)

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Grzegorz Jaśkiewicz]

That's one of things pg xml type lacks ... :/
I just need that to get some real xml, and convert to table once, so I
should be fine with xpath, but ... heh. This is so ugly.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Pavel Stehule]

2009/6/1 Grzegorz Jaśkiewicz gryz...@gmail.com:
 That's one of things pg xml type lacks ... :/

yes - SQL/XML isn't completed yet

http://wiki.postgresql.org/wiki/XML_Support :(

I believe so some procedure like xml_to_table should be nice.

but plperlu code should be simple (as perl code should be :)) and fast



 I just need that to get some real xml, and convert to table once, so I
 should be fine with xpath, but ... heh. This is so ugly.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml to table (as oppose to table to xml)

[Scott Bailey]

Pavel Stehule wrote:

2009/6/1 Grzegorz Jaśkiewicz gryz...@gmail.com:

That's one of things pg xml type lacks ... :/


yes - SQL/XML isn't completed yet

http://wiki.postgresql.org/wiki/XML_Support :(

I believe so some procedure like xml_to_table should be nice.

but plperlu code should be simple (as perl code should be :)) and fast




I just need that to get some real xml, and convert to table once, so I
should be fine with xpath, but ... heh. This is so ugly.


I started to blog on exactly this but didn't get around to finishing it. 
Seeing the article on Postgres Online Journal reminded me how clunky the 
original pg style is. This is an Postgres adaption of an Oracle 
function. It should make your queries cleaner.


CREATE OR REPLACE FUNCTION extract_value(
   VARCHAR,
   XML
) RETURNS text AS
$$
   SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$'
   THEN (xpath($1, $2))[1]
   WHEN $1 ~* '/text()$'
   THEN (xpath($1, $2))[1]
   WHEN $1 LIKE '%/'
   THEN (xpath($1 || 'text()', $2))[1]
   ELSE (xpath($1 || '/text()', $2))[1]
   END::text;
$$ LANGUAGE 'sql' IMMUTABLE;

Use it like so:

SELECT extract_value('/wpt/name', node)  AS name,
   extract_value('@lon', node)::numeric AS lon,
   extract_value('@lat', node)::numeric AS lat,
   extract_value('/wpt/ele', node)::numeric AS ele
FROM (
   SELECT unnest(xpath('/gpx/wpt', object_value)) AS node
   FROM gpx
   WHERE object_name = 'fellsLoop'
) sub

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML - PG ?

[Gauthier, Dave]

Is there a way to read an XML file into a postgres DB?  I'm thinking that it 
will create and relate whatever tables are necessary to reflect whatever's 
implied by the XML file structure.

Thanks for any pointers !

Re: [GENERAL] XML - PG ?

[Merlin Moncure]

On Wed, May 6, 2009 at 10:47 AM, Gauthier, Dave dave.gauth...@intel.com wrote:
 Is there a way to read an XML file into a postgres DB?  I’m thinking that it
 will create and relate whatever tables are necessary to reflect whatever’s
 implied by the XML file structure.

since xml is basically completely unstructured, you are not giving
enough information about what you'd like to do. that said, be sure and
check out the pg xml extensions...

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Christophe]

On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:

Is there a way to read an XML file into a postgres DB?  I’m thinking  
that it will create and relate whatever tables are necessary to  
reflect whatever’s implied by the XML file structure.


There's no built-in functionality that does what you describe,  
although building such a thing would be very straight-forward. There  
are many application-specific decisions you'd need to make (what level  
of hierarchy in the XML file corresponds to a database/schema/table,  
for example, and how to handle nested fields and missing ones).

Re: [GENERAL] XML - PG ?

[Gauthier, Dave]

Maybe...

ALL
  EMPLOYEES
EMP EMP_NAME=JOE JOB=CARPENTER /
EMP EMP_NAME=FRANK JOB=PLUMBER/
EMP EMP_NAME=SUE JOB=CARPENTER/
  /EMPLOYEES
  JOBS
JOB JOB_NAME=CARPENTER SALARY=25.50 /
JOB JOB_NAME=PLUMBER SALARY=28.75 /
  /JOBS
/ALL

...equals...

create table employees (emp_name varchar[64], job varchar[64]);
create table jobs (job_name varchar[64], salary float);
insert into employees (emp_name,job) values ('JOE','CARPENTER');
insert into employees (emp_name,job) values ('FRANK','PLUMBER');
insert into employees (emp_name,job) values ('SUE','CARPENTER');
insert into jobs (job_name,salary) values (CARPENTER,25.50);
insert into jobs (job_name,salary) values (PLUMBER,28.75);

After that, it's up to the user to understand that employees.job = 
jobs.job_name and index appropriately.





-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com] 
Sent: Wednesday, May 06, 2009 11:23 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] XML - PG ?

On Wed, May 6, 2009 at 10:47 AM, Gauthier, Dave dave.gauth...@intel.com wrote:
 Is there a way to read an XML file into a postgres DB?  I'm thinking that it
 will create and relate whatever tables are necessary to reflect whatever's
 implied by the XML file structure.

since xml is basically completely unstructured, you are not giving
enough information about what you'd like to do. that said, be sure and
check out the pg xml extensions...

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Joao Ferreira gmail]

hello,

as a perl addict I am... I recommend checking this out:

http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm

it's pretty flexible and allows you to specify to some extent just how
the database structure is infered from the XML...

check it out

Joao




On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote:
 
 On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
 
  Is there a way to read an XML file into a postgres DB?  I’m thinking
  that it will create and relate whatever tables are necessary to
  reflect whatever’s implied by the XML file structure.
 
 
 There's no built-in functionality that does what you describe,
 although building such a thing would be very straight-forward. There
 are many application-specific decisions you'd need to make (what level
 of hierarchy in the XML file corresponds to a database/schema/table,
 for example, and how to handle nested fields and missing ones).


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Joao Ferreira gmail]

On Wed, 2009-05-06 at 16:53 +0100, Joao Ferreira gmail wrote:
 hello,
 
 as a perl addict I am... I recommend checking this out:
 
 http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm
 
 it's pretty flexible and allows you to specify to some extent just how
 the database structure is infered from the XML...


... maybe start here to get a faster grasp:

http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag.pm

j


 
 check it out
 
 Joao
 
 
 
 
 On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote:
  
  On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
  
   Is there a way to read an XML file into a postgres DB?  I’m thinking
   that it will create and relate whatever tables are necessary to
   reflect whatever’s implied by the XML file structure.
  
  
  There's no built-in functionality that does what you describe,
  although building such a thing would be very straight-forward. There
  are many application-specific decisions you'd need to make (what level
  of hierarchy in the XML file corresponds to a database/schema/table,
  for example, and how to handle nested fields and missing ones).
 
 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Thomas Kellerer]

Gauthier, Dave, 06.05.2009 17:40:

Maybe...

ALL
  EMPLOYEES
EMP EMP_NAME=JOE JOB=CARPENTER /
EMP EMP_NAME=FRANK JOB=PLUMBER/
EMP EMP_NAME=SUE JOB=CARPENTER/
  /EMPLOYEES
  JOBS
JOB JOB_NAME=CARPENTER SALARY=25.50 /
JOB JOB_NAME=PLUMBER SALARY=28.75 /
  /JOBS
/ALL

...equals...

create table employees (emp_name varchar[64], job varchar[64]);
create table jobs (job_name varchar[64], salary float);
insert into employees (emp_name,job) values ('JOE','CARPENTER');
insert into employees (emp_name,job) values ('FRANK','PLUMBER');
insert into employees (emp_name,job) values ('SUE','CARPENTER');
insert into jobs (job_name,salary) values (CARPENTER,25.50);
insert into jobs (job_name,salary) values (PLUMBER,28.75);



You could use XSLT to tranform the XML into the approriate SQL Statements.

Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Joao Ferreira]

On Wed, 2009-05-06 at 16:53 +0100, Joao Ferreira gmail wrote:
 hello,
 
 as a perl addict I am... I recommend checking this out:
 
 http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm
 
 it's pretty flexible and allows you to specify to some extent just how
 the database structure is infered from the XML...
 

maybe start here to get a faster grasp:

http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag.pm

j


 check it out
 
 Joao
 
 
 
 
 On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote:
  
  On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
  
   Is there a way to read an XML file into a postgres DB?  I’m thinking
   that it will create and relate whatever tables are necessary to
   reflect whatever’s implied by the XML file structure.
  
  
  There's no built-in functionality that does what you describe,
  although building such a thing would be very straight-forward. There
  are many application-specific decisions you'd need to make (what level
  of hierarchy in the XML file corresponds to a database/schema/table,
  for example, and how to handle nested fields and missing ones).
 
 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Eric Schwarzenbach]

Gauthier, Dave wrote:

 Is there a way to read an XML file into a postgres DB?  I’m thinking
 that it will create and relate whatever tables are necessary to
 reflect whatever’s implied by the XML file structure.

  

 Thanks for any pointers !

That's a pretty common problem, and not one that needs to have a
postgresql-specific solution. There are definitely solutions out there,
but it may take some Googling to find a good one for your needs. One
option might be data binding tools (Castor and Liquid XML come to mind)
with which you can definitely go from XML to SQL, though many of them
may require going through an intermediate object model.

A simple approach (simple depending what technologies you're using and
are already familiar with) might be to use XSLT to transform your XML
either directly into SQL or into some other format easily loaded to a
database (such as the XML format used by DbUnit).

Eric

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[John R Pierce]

Gauthier, Dave wrote:


Is there a way to read an XML file into a postgres DB? I’m thinking 
that it will create and relate whatever tables are necessary to 
reflect whatever’s implied by the XML file structure.


Thanks for any pointers !



As others have said, the fundamental problem is that generalized XML is 
freeform and inherently unstructured. Every piece of data can have a 
different structure. IMHO, XML is fundamentally flawed by mixing the 
metadata with the data.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML - PG ?

[Ries van Twisk]

On May 6, 2009, at 4:16 PM, Eric Schwarzenbach wrote:


Gauthier, Dave wrote:


Is there a way to read an XML file into a postgres DB?  I’m thinking
that it will create and relate whatever tables are necessary to
reflect whatever’s implied by the XML file structure.



Thanks for any pointers !


That's a pretty common problem, and not one that needs to have a
postgresql-specific solution. There are definitely solutions out  
there,

but it may take some Googling to find a good one for your needs. One
option might be data binding tools (Castor and Liquid XML come to  
mind)

with which you can definitely go from XML to SQL, though many of them
may require going through an intermediate object model.

A simple approach (simple depending what technologies you're using and
are already familiar with) might be to use XSLT to transform your XML
either directly into SQL or into some other format easily loaded to a
database (such as the XML format used by DbUnit).

Eric



Call me a GUI boy, but I use JasperETL :)

Ries





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] xml not enabled by default on rhel4 packages from commandprompt

[Grzegorz Jaśkiewicz]

Any idea why xml support is off ?
The libxml2, version 2.6.23 is there on centos4.7 (which is what I am
using), is there any known problem with xml that it is off, or is just
because they wanted to make sure that the package is going to work in
versions prior to 4.7 as well ??

any ideas ?

-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Peter Eisentraut]

Tom Lane wrote:

Jef Peeraer [EMAIL PROTECTED] writes:

i am using the xml add-ons, but the date output format seems to be wrong :


I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.


Yes, it follows XML Schema.  Which is why the output format is even 
slightly different from the SQL-mandated ISO format.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Jef Peeraer]

On Thu, 11 Sep 2008, Peter Eisentraut wrote:

 Tom Lane wrote:
  Jef Peeraer [EMAIL PROTECTED] writes:
   i am using the xml add-ons, but the date output format seems to be wrong :
  
  I think the conversion to xml intentionally always uses ISO date format,
  because that's required by some spec somewhere.
 
 Yes, it follows XML Schema.  Which is why the output format is even slightly
 different from the SQL-mandated ISO format.
i understand, but that makes it very difficult to change the date format 
afterwards. i simple flag to indicate no date conversion would be 
helpfull
  
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 
 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Pavel Stehule]

2008/9/11 Jef Peeraer [EMAIL PROTECTED]:


 On Thu, 11 Sep 2008, Peter Eisentraut wrote:

 Tom Lane wrote:
  Jef Peeraer [EMAIL PROTECTED] writes:
   i am using the xml add-ons, but the date output format seems to be wrong 
   :
 
  I think the conversion to xml intentionally always uses ISO date format,
  because that's required by some spec somewhere.

 Yes, it follows XML Schema.  Which is why the output format is even slightly
 different from the SQL-mandated ISO format.
 i understand, but that makes it very difficult to change the date format
 afterwards. i simple flag to indicate no date conversion would be
 helpfull
  

no, use explicit casting to varchar

-- xml formating style
postgres=# select xmlforest(current_timestamp as date);
   xmlforest
---
 date2008-09-11T12:21:44.600512+02:00/date
(1 row)

postgres=# select xmlforest(current_timestamp::text as date);
 xmlforest

 date2008-09-11 12:22:25.180611+02/date
(1 row)

postgres=# set datestyle to German ;
SET
postgres=# select xmlforest(current_timestamp::text as date);
  xmlforest
--
 date11.09.2008 12:22:32.947672 CEST/date
(1 row)

regards
Pavel Stehule


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Peter Eisentraut]

Jef Peeraer wrote:


On Thu, 11 Sep 2008, Peter Eisentraut wrote:


Tom Lane wrote:

Jef Peeraer [EMAIL PROTECTED] writes:

i am using the xml add-ons, but the date output format seems to be wrong :

I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.

Yes, it follows XML Schema.  Which is why the output format is even slightly
different from the SQL-mandated ISO format.
i understand, but that makes it very difficult to change the date format 
afterwards. i simple flag to indicate no date conversion would be 
helpfull


Well, these table_to_xml etc. functions are heavily constrained by the 
SQL standard, XML Schema, and others.  They do what they are supposed to 
do.  You are free to design your own XML export format or apply 
postprocessing to the existing ones (XSLT?).  I don't think we should 
overload the existing functions with everyone's favorite but apparently 
completely nonstandard formatting variant flag.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Jef Peeraer]

On Thu, 11 Sep 2008, Peter Eisentraut wrote:

 Jef Peeraer wrote:
  
  On Thu, 11 Sep 2008, Peter Eisentraut wrote:
  
   Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
 i am using the xml add-ons, but the date output format seems to be
 wrong :
I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.
   Yes, it follows XML Schema.  Which is why the output format is even
   slightly
   different from the SQL-mandated ISO format.
  i understand, but that makes it very difficult to change the date format
  afterwards. i simple flag to indicate no date conversion would be
  helpfull
 
 Well, these table_to_xml etc. functions are heavily constrained by the SQL
 standard, XML Schema, and others.  They do what they are supposed to do.  You
 are free to design your own XML export format or apply postprocessing to the
 existing ones (XSLT?).  I don't think we should overload the existing
 functions with everyone's favorite but apparently completely nonstandard
 formatting variant flag.
it would be a flag to indicate no conversion from the datestyle settings 
in the database...i think, from a users perspective, the table_to_xml is 
completely useless, if you have to reformat everything afterwards

jef
  
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Pavel Stehule]

2008/9/11 Jef Peeraer [EMAIL PROTECTED]:


 On Thu, 11 Sep 2008, Peter Eisentraut wrote:

 Jef Peeraer wrote:
 
  On Thu, 11 Sep 2008, Peter Eisentraut wrote:
 
   Tom Lane wrote:
Jef Peeraer [EMAIL PROTECTED] writes:
 i am using the xml add-ons, but the date output format seems to be
 wrong :
I think the conversion to xml intentionally always uses ISO date 
format,
because that's required by some spec somewhere.
   Yes, it follows XML Schema.  Which is why the output format is even
   slightly
   different from the SQL-mandated ISO format.
  i understand, but that makes it very difficult to change the date format
  afterwards. i simple flag to indicate no date conversion would be
  helpfull

 Well, these table_to_xml etc. functions are heavily constrained by the SQL
 standard, XML Schema, and others.  They do what they are supposed to do.  You
 are free to design your own XML export format or apply postprocessing to the
 existing ones (XSLT?).  I don't think we should overload the existing
 functions with everyone's favorite but apparently completely nonstandard
 formatting variant flag.
 it would be a flag to indicate no conversion from the datestyle settings
 in the database...i think, from a users perspective, the table_to_xml is
 completely useless, if you have to reformat everything afterwards

I am not sure - mostly people should generate valid xml file.
Sending invalid dates in XML is wrong.

Pavel


 jef
  
 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Peter Eisentraut]

Jef Peeraer wrote:
it would be a flag to indicate no conversion from the datestyle settings 
in the database...i think, from a users perspective, the table_to_xml is 
completely useless, if you have to reformat everything afterwards


Just write a function that does your formatting afterwards.  You can 
even name it table_to_xml.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] xml queries date format

[Jef Peeraer]

i am using the xml add-ons, but the date output format seems to be wrong :
i have 
show datestyle;
 DateStyle
---
 SQL, DMY

select agenda_datum from dossiers where id = 61;
 agenda_datum
--
 29/07/2008

select table_to_xml('dossiers', false, false, '');
gives  (knip )
row
   id62/id
   voorwerp_detail5 coils 5.622 kg/voorwerp_detail
   schade_datum2008-07-29/schade_datum
   voorbehoudfalse/voorbehoud
   protestfalse/protest
   vorderingfalse/vordering
   afgewezenfalse/afgewezen
   gedeeltelijk_afgewezenfalse/gedeeltelijk_afgewezen
   verhaalfalse/verhaal
   administratieffalse/administratief
 /row



jef

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml queries date format

[Tom Lane]

Jef Peeraer [EMAIL PROTECTED] writes:
 i am using the xml add-ons, but the date output format seems to be wrong :

I think the conversion to xml intentionally always uses ISO date format,
because that's required by some spec somewhere.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] xml and postgresql

[Gwyneth Morrison]

aravind chandu wrote:

  

  
Hi folks,


I need to load xml data in to database can you tell me they way how do
I import xml data into postgresql database.

lets dat this is the xml file

bookstore
book category="CHILDREN"
  titleHarry Potter/title 
  authorJ K. Rowling/author 
  year2005/year 
  price29.99/price 
/book
book category="WEB"
  titleLearning XML/title 
  authorErik T. Ray/author 
  year2003/year 
  price39.95/price 
/book
/bookstore

so finally the table name should be bookstore and column names are category,title,
author,year,price and all the information in the xml file should be ported to the table 
can you please tell me how to do this its a bit confusing to me as i am just a beginner to 
this.

Thanks in advance,
Aravind.




  

  
  

I have written a python program that may help you.

It is part of a larger project I hope to post in a few days. 

Or I could sent it to you.

Gwyneth

Re: [GENERAL] xml and postgresql

[William Leite Araújo]

On Fri, Jul 4, 2008 at 11:01 AM, Gwyneth Morrison [EMAIL PROTECTED]
wrote:

  aravind chandu wrote:

   Hi folks,

  I need to load xml data in to database can you tell me they
 way how do I import xml data into postgresql database.

 lets dat this is the xml file

 bookstore
 book category=CHILDREN

Why category  is not a Element of book, as all others? Is it a
column of table bookstore?



   titleHarry Potter/title
   authorJ K. Rowling/author
   year2005/year
   price29.99/price
 /book
 book category=WEB
   titleLearning XML/title
   authorErik T. Ray/author
   year2003/year
   price39.95/price
 /book
 /bookstore

 so finally the table name should be bookstore and column names are 
 category,title,
 author,year,price and all the information in the xml file should be ported to 
 the table
 can you please tell me how to do this its a bit confusing to me as i am just 
 a beginner to
 this.

 Thanks in advance,
 Aravind.





  I have written a python program that may help you.

 It is part of a larger project I hope to post in a few days.

 Or I could sent it to you.

 Gwyneth




-- 
William Leite Araújo
Pai 0.6 beta 2.1 Dizem que agora melhora...

[GENERAL] xml and postgresql

[aravind chandu]

Hi folks,




I need to load xml data in to database can you tell me they way how do
I import xml data into postgresql database.



lets dat this is the xml file



bookstore
book category=CHILDREN
  titleHarry Potter/title 
  authorJ K. Rowling/author 
  year2005/year 
  price29.99/price 
/book
book category=WEB
  titleLearning XML/title 
  authorErik T. Ray/author 
  year2003/year 
  price39.95/price 
/book
/bookstore

so finally the table name should be bookstore and column names are 
category,title,
author,year,price and all the information in the xml file should be ported to 
the table 
can you please tell me how to do this its a bit confusing to me as i am just a 
beginner to 
this.

Thanks in advance,
Aravind.








  

Re: [GENERAL] XML output multiple SELECT queries

[Peter Eisentraut]

Peter Billen wrote:
 I would like to ask a question about outputting data as XML. Say I have two
 tables:

 team(integer id, text name);
 player_of_team(integer id, integer team_id, text name); (team_id is FK to
 team.id)

 I would like to query both tables to get following example XML output:

 team name=Real Madrid
      players
          nameGarcia/name
          nameRobinho/name
      /players
 /team

SELECT XMLElement(name team, XMLAttributes(team.name as name), XMLElement(name 
players, XMLAgg(XMLElement(name name, player_of_team.name FROM team JOIN 
player_of_team ON team.id = player_of_team.team_id GROUP BY team.name;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML output multiple SELECT queries

[Peter Billen]

Dear PostgreSQL users,

I would like to ask a question about outputting data as XML. Say I have two
tables:

team(integer id, text name);
player_of_team(integer id, integer team_id, text name); (team_id is FK to
team.id)

I would like to query both tables to get following example XML output:

team name=Real Madrid
 players
 nameGarcia/name
 nameRobinho/name
 /players
/team

Is this possible in one query? I'm thinking about something like this, but I
haven't figured it out so far:

SELECT XMLElement(name team, XMLAttributes(name as name), SELECT XMLElement(name
players, XMLForest(name)) FROM player_of_team WHERE team_id = t.id) FROM team t
WHERE t.name = 'Real Madrid';

In other words, is it possible to nest multiple SELECT queries in the XML output
functions?

Thanks in advance. Kind regards,

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Support related questions

[Peter Eisentraut]

Brijesh Shrivastav wrote:
 For #4 I was looking to be able to index some or all of the tags in the
 xml document. Most of our applications query very few tags in a Xml
 document
 and a smaller index on few tags will help with query performance.

Expression indexes on xpath are probably what you should look at.

 Any guess on timeframe for #2, #3 and #4?

No.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML Support related questions

[Peter Eisentraut]

Am Donnerstag, 22. Mai 2008 schrieb Brijesh Shrivastav:
 1)  Can xml column be constrained to be DOCUMENT or CONTENT type?

Using a check constraint that does IS [NOT] DOCUMENT on the value.

 2)  Is there plan in near future to support XML schema validation
 i.e to ensure inserted xml document conforms to a preregistered set of
 XML schemas.

Plans yes, but I don't think anyone is working on it at the moment.

 3)  Support for XQuery - I know it is asking for too much but when
 do you see it happening in the future.

See #2.

 4)  Support for xml indexes - Is it something that is being worked
 upon for next release?

See #3. ;-)  Of course this question is a bit underspecified, because indexes 
depend on some operators, and XML does not have any operators at the moment, 
so what do you want to index?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general