[SQL] Create index on xml field

2009-01-13 Thread Brad Balmer 
Could somebody please help me with the correct syntax for creating an index on 
an xml field. My (simple) table format is: 

CREATE TABLE test_tbl (id_cd integer not null, job xml); 

INSERT INTO test_tbl VALUES (200, 
'?xml version=1.0 encoding=UTF-8?uim:hd 
xmlns:uim=http://www.cmpy.com/uim;uim:baseDatauim:internalProductId202/uim:internalProductIduim:upcCodesuim:upcCode0381370036006/uim:upcCodeuim:upcCode01236/uim:upcCode/uim:upcCodes/uim:baseData/uim:hd');
 

I've tried multiple ways of creating an idex on the uim:upcCode element but 
always get an ERROR: could not create XPath object. 

Why would the following not work? 
create index tstTbl_idx on test_tbl (cast(xpath ('//uim:upcCode/text()', job) 
as text[])); 

Thanks

Re: [SQL] Create index on xml field

2009-01-13 Thread Peter Eisentraut 
On Tuesday 13 January 2009 18:56:33 Brad Balmer wrote:
 Why would the following not work?
 create index tstTbl_idx on test_tbl (cast(xpath ('//uim:upcCode/text()',
 job) as text[]));

Looks like you are missing a namespace definition.

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

Re: [SQL] some howto/theory book/tutorial on practical problem solving in SQL

2009-01-13 Thread Erik Jones 


On Jan 11, 2009, at 8:32 AM, Ivan Sergio Borgonovo wrote:


I'm looking to some book/tutorial/sample code that will teach me how
to use SQL to solve some standard problem that goes a bit beyond
using group by and aggregates.

Something like SQL problem solving or SQL design strategies for
selected problems.


O'Reilly's SQL Hacks is a good one that fits the bill you describe.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






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