Generating XPATHS from XML docs

Assuming a table called CLAIM_P with following structure where EDI is the XML column:

Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TRACER_ID SYSIBM DECIMAL 11 0 No
SYS_CREATE_DT SYSIBM DATE 4 0 Yes
EDI SYSIBM XML 0 0 Yes

The following query in DB2 9.7 with DB2_COMPATIBILITY_VECTOR set to FFFF should return all distinct XPATHS found in all the XML's in the EDI column for one given row (not all rows).

WITH ELEMENT_TREE(SEQ, ELE, PAR) AS (SELECT T.*
FROM NASCO.CLAIM_P P,
XMLTABLE('$claim//*' PASSING P.EDI as "claim"
COLUMNS
seq FOR ORDINALITY,
element VARCHAR(20) PATH 'name(.)',
parent VARCHAR(20) PATH '../name(.)') AS T
where p.tracer_id=1)
SELECT distinct SYS_CONNECT_BY_PATH(Ele, '\') AS chain
FROM ELEMENT_TREE
START WITH SEQ = 1
CONNECT BY PRIOR ELE = PAR


To do this for several rows in a table, could only think of doing it from a stored proc as I was not able to figure out how to link the XMLTABLE to the regular table.

CREATE PROCEDURE INSERT_XPATHS()
LANGUAGE SQL
BEGIN
DECLARE x int;
DECLARE part1 varchar(1000);
DECLARE part2 varchar(1000);

SET part1 = 'insert into xpaths '||
' WITH ELEMENT_TREE(SEQ, ELE, PAR) AS (SELECT T.* '||
' FROM NASCO.CLAIM_P P,' ||
' XMLTABLE(''$claim//*'' PASSING P.EDI as "claim" '||
' COLUMNS '||
' seq FOR ORDINALITY, '||
' element VARCHAR(20) PATH ''name(.)'', '||
' parent VARCHAR(20) PATH ''../name(.)'') AS T '||
' where p.tracer_id= ';

SET part2 = ' ),'||
' ABC(chain) AS ('||
' SELECT distinct SYS_CONNECT_BY_PATH(Ele, ''\'') AS chain '||
' FROM ELEMENT_TREE'||
' START WITH SEQ = 1 '||
' CONNECT BY PRIOR ELE = PAR '||
' )'||
' SELECT chain FROM ABC ';
SET x = (SELECT count(*) from NASCO.CLAIM_P);
WHILE (x>0) DO
EXECUTE IMMEDIATE part1||x||part2;
commit;

SET x = x-1;
END WHILE;
END

Db2 doesn't start, CLP doesn't start. Error is SQL6031N Error in the db2nodes.cfg file at line number "0". Reason code "3".

There could be a problem with the db2nodes.cfg file. I had an entry like:
0 RAMSCOMP RAMSCOMP 0
and it was causing all the trouble. Issuing a
db2ncrt /n:0 /u:user,password
fixed the problem. It took out the last 0 from the db2nodes.cfg file. Don't know how the last 0 got in in the first place but I guess it had something to do with my trying to edit the services file and change the db2 port numbers as everything was working fine until then.

Detailed list of things to do to change hostname:
http://www-01.ibm.com/support/docview.wss?uid=swg21258834

http://www.thefillmoregroup.com/blog/?p=66