Re: Joining different (nested) levels of an XML

ccote_msl_at_yahoo.com wrote in message news:<1110839694.835163.171530_at_l41g2000cwc.googlegroups.com>...
> HI, I have an XMLType column that ha the following XML structure:
>
> <Client>
> <Report id = "01">
> <Item id = "01" Description = ",a.mdfbnelt;yuk" </Item>
> <Item id = "02" Description = "AAAAA;yuk" </Item>
> <Item id = "03" Description = "XXXXX" </Item>
> </Report>
> <Report id = "02">
> <Item id = "01" Description = ",ABCDEF" </Item>
> <Item id = "02" Description = "JHIKLM" </Item>
> <Item id = "03" Description = "OPQRST" </Item>
> </Report>
> </Client>
>
> I would like to extract and insert data into the following file
>
> Table MyTable(Client, Report_id, Item_id, Description)
>
> So I created the following query
> SELECT
> extractValue(VALUE(x), '/Client/_at_id')) Client,
> extractValue(VALUE(d), '/Report/_at_id') Report,
> extractValue(VALUE(e), '/Item/_at_id') Description,
> extractValue(VALUE(e), '/Item/_at_Description) Description
> FROM XMLDOC2 x,
> TABLE(xmlsequence(extract(VALUE(x), '/Client/Report'))) d,
> TABLE(xmlsequence(extract(VALUE(x), '/Client/Report/Item'))) e
>
> But I get the cartesian product of the reports and the items. I would
> like to get the items related to a reports AND the report id on the
> same line. If it is not possible, I would like to filter out the items
> belonging to report; some kind of where clause.
>
> Anybody has an idea?
>
> Thank you,
> CCote

Ok, I found out how. I use two FOR loops (implicit cursors). One for the Reports and one for the Items.

I the filter out the Items with
TABLE(xmlsequence(extract(VALUE(x),
'/Client/Report["'||Report_rec.report_ID||'"]/Item')))

And I get only the Items related to a specific report. Received on Wed Mar 16 2005 - 14:31:13 CET