Thread: Create index on xml field

Create index on xml field

From

Brad Balmer

Date:

13 January 2009, 16:07:42

<div style="font-family: Times New Roman; font-size: 12pt; color: #000000">Could somebody please help me with the
correctsyntax for creating an index on an xml field.  My (simple) table format is:<br /><br />CREATE TABLE test_tbl
(id_cdinteger not null, job xml);<br /><br />INSERT INTO test_tbl VALUES (200,<br />'<?xml version="1.0"
encoding="UTF-8"?><uim:hd
xmlns:uim="http://www.cmpy.com/uim"><uim:baseData><uim:internalProductId>202</uim:internalProductId><uim:upcCodes><uim:upcCode>0381370036006</uim:upcCode><uim:upcCode>0000000001236</uim:upcCode></uim:upcCodes></uim:baseData></uim:hd>');<br
/><br/>I've tried multiple ways of creating an idex on the <uim:upcCode> element but always get an ERROR:  could
notcreate XPath object.<br /><br />Why would the following not work?<br />create index tstTbl_idx on test_tbl
(cast(xpath('//uim:upcCode/text()', job) as text[]));<br /><br />Thanks<br /></div>

Re: Create index on xml field

From

Peter Eisentraut

Date:

13 January 2009, 18:42:32

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.