Joining different (nested) levels of an XML

[Guest]

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/@id')) Client,
extractValue(VALUE(d), '/Report/@id') Report,
extractValue(VALUE(e), '/Item/@id') Description,
extractValue(VALUE(e), '/Item/@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

[Christian]

ccote_msl@yahoo.com wrote in message news:<1110839694.835163.171530@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/@id')) Client, extractValue(VALUE(d), '/Report/@id') Report, extractValue(VALUE(e), '/Item/@id') Description, extractValue(VALUE(e), '/Item/@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.