[GENERAL] xpath() subquery for empty array
In postgres 8.4 When running xpath() queries it seems that empty results
are always returned. So if I query a table containing 1000 XML documents
a 1000 rows will always be fetched even if the xpath() element of the
query only matches 10 documents.
The documentation states:
The function |xpath| evaluates the XPath expression /xpath/ against
the XML value /xml/. It returns an array of XML values corresponding
to the node set produced by the XPath expression.
Yet it seems that empty node sets (arrays) are also returned. So, my
thought was to run a subquery to eliminate empty arrays, e.g.:
SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x [test for empty array?]
How do I test for an empty array in postgres?
Thanks in advance,
Roy
Re: [GENERAL] xpath() subquery for empty array
Scott Bailey wrote:
Roy Walter wrote:
In postgres 8.4 When running xpath() queries it seems that empty
results are always returned. So if I query a table containing 1000
XML documents a 1000 rows will always be fetched even if the xpath()
element of the query only matches 10 documents.
The documentation states:
The function |xpath| evaluates the XPath expression /xpath/ against
the XML value /xml/. It returns an array of XML values corresponding
to the node set produced by the XPath expression.
Yet it seems that empty node sets (arrays) are also returned. So, my
thought was to run a subquery to eliminate empty arrays, e.g.:
SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*',
docxml)
AS x FROM docs) AS y WHERE x [test for empty array?]
How do I test for an empty array in postgres?
Thanks in advance,
Roy
WHERE x != array[]::xml[]
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.387 / Virus Database: 270.13.12/2233 - Release Date: 07/12/09 08:20:00
Thanks Scott but that throws up a syntax error (at the closing bracket
of array[]):
ERROR: syntax error at or near ]
LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[]
^
-- Roy
Re: [GENERAL] xpath() subquery for empty array
Scott Bailey wrote:
Sam Mason wrote:
On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote:
Scott Bailey wrote:
Roy Walter wrote:
How do I test for an empty array in postgres?
WHERE x != array[]::xml[]
Thanks Scott but that throws up a syntax error (at the closing
bracket of array[]):
ERROR: syntax error at or near ]
LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[]
Even if that syntax was correct it wouldn't work, xml values don't have
an equality operator defined for them. I've normally tested the array
size to figure out when they're empty, something like:
array_upper($1,1) 0
However, I've just noticed that this returns NULL rather than zero as
I was expecting for an empty array (i.e. the literal '{}'). It also
doesn't seem to do useful things if you're using unusual bounds on your
array.
Bah, the semantics of arrays in PG always seem over-complicated to me!
This worked on both 8.3 and 8.4
SELECT *
FROM (
VALUES( '{}'::_xml),('{root/}'::_xml)
) sub
WHERE array_upper(column1, 1) 0Sam Mason s...@samason.me.uk
And as Sam noted, array_upper needs to return null if the array is
empty because 0 and -1 can be valid indexes for arrays in postgres.
Thanks, that's great, I'm delighted that there's a solution. Where
exactly does that fit in terms of my original query, i.e.:
SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x [test for empty array?]
-- Roy
Re: [GENERAL] xpath() subquery for empty array
Sam Mason wrote:
On Sun, Jul 12, 2009 at 09:49:15PM +0100, Roy Walter wrote:
Where exactly does that fit in terms of my original query, i.e.:
SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x [test for empty array?]
Something like the following should do the right thing:
SELECT x FROM (
SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x
FROM docs) AS y
WHERE array_upper(x, 1) 0;
Thank you, works just fine. I thought it was more complicated than that :-)
-- Roy
Re: [GENERAL] XML import with DTD
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
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
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
