By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,318 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,318 IT Pros & Developers. It's quick & easy.

beginner XML extract question

P: n/a
ab
I have the following table :
<id , itemswhere each element in the items column is of the
following form
<itemi_1 </item<itemi_2 </item>... <itemi_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!

..

Jun 15 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Jun 17 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.