XML joining Nested resultsets

[ccote_msl@yahoo.com]

HI, I have an XMLType column that has the following XML structure:


<Client>
<Report id = "01">
<Item id = "01" Description = ",a.mdfbnelt;yu k" </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(VA LUE(x), '/Client/@id')) Client,
extractValue(VA LUE(d), '/Report/@id') Report,
extractValue(VA LUE(e), '/Item/@id') Description,
extractValue(VA LUE(e), '/Item/@Description) Description
FROM XMLDOC2 x,
TABLE(xmlsequen ce(extract(VALU E(x), '/Client/Report'))) d,
TABLE(xmlsequen ce(extract(VALU E(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


Reply

[GreyBeard]

Re: XML joining Nested resultsets

http://groups.google.ca/groups?hl=en&lr=&group=comp.databases.oracle&selm=pan.2005.03.09.14.15.32.110766%40gmail.com