There may be a solution using DB2 9 XML functionalities.
But, I don't know them nor I have DB2 9 test environment.
Here is an example without using XML functionalities.
CREATE TABLE XML_stored
(ID INTEGER NOT NULL PRIMARY KEY
,item VARCHAR(1000) NOT NULL
);
INSERT INTO XML_stored
VALUES
(1, '<itemi_1 </item<itemi_2 </item<itemi_3 </item<item>
i_4 </item<itemi_5 </item>')
,(2, '<itemi_1 </item<itemi_2 </item<itemi_3 </item<item>
i_4 </item<itemi_5 </item<itemi_6 </item<itemi_7 </
item>');
-------------------- Commands Entered --------------------
WITH numbers (n) AS (
SELECT INTEGER(ROWNUMBER() OVER() )
FROM (VALUES 0,1,2,3,4,5,6,7,8,9) P1(n)
, (VALUES 0,1,2,3,4,5,6,7,8,9) P2(n)
, (VALUES 0,1,2,3,4,5,6,7,8,9) P2(n)
)
SELECT id
, CAST(SUBSTR(item, b.n+6, e.n-b.n-6) AS VARCHAR(10)) item
FROM XML_stored x
, numbers b
, TABLE
(SELECT MIN(e.n)
FROM numbers e
WHERE e.n b.n
AND SUBSTR(item,e.n,7) = '</item>'
AND e.n < LENGTH(x.item) - 5
) e(n)
WHERE SUBSTR(item,b.n,6) = '<item>'
AND b.n < LENGTH(x.item) - 10
ORDER BY id, b.n;
----------------------------------------------------------
ID ITEM
----------- ----------
1 i_1
1 i_2
1 i_3
1 i_4
1 i_5
2 i_1
2 i_2
2 i_3
2 i_4
2 i_5
2 i_6
2 i_7
12 record(s) selected.