I have the following table :
<id , items> where each element in the items column is of the
following form
<item> i_1 </item> <item> i_2 </item>... <item> i_n </item>. I need to
query the data such that I can have the following :

<id, item>
id1 i_1
id1 i_2
..
..
..
id1 i_n.
id2 i_1...
...etc.

Currently items is stored as a varchar. What would be a good way to do
this?
Thanks!

..

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, '<item> i_1 </item> <item> i_2 </item> <item> i_3 </item> <item>
i_4 </item> <item> i_5 </item>')
,(2, '<item> i_1 </item> <item> i_2 </item> <item> i_3 </item> <item>
i_4 </item> <item> i_5 </item> <item> i_6 </item> <item> i_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.