Metacat

/* Move data to the table 'temp'*/

CREATE TABLE temp AS 

	Select * from xml_index;

DROP TABLE xml_index;

/* Create the table again */

CREATE TABLE xml_index (

	nodeid INT8,		-- the unique node id

	path VARCHAR(1000),	-- precomputed path through tree

	docid VARCHAR(250),	-- index to the document id

	doctype VARCHAR(100),	-- public id indicating document type

        parentnodeid INT8,     -- id of the parent of the node represented

					-- by this row

   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),

   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,

   CONSTRAINT xml_index_docid_fk 

		FOREIGN KEY (docid) REFERENCES xml_documents

);

CREATE INDEX xml_index_idx1 ON xml_index (path);

/* Insert data */

INSERT INTO xml_index (nodeid, path, docid, doctype, parentnodeid)

	SELECT nodeid, path, docid, doctype, parentnodeid

		from temp;

/* Drop temp table */

DROP TABLE temp;