Joining different (nested) levels of an XML
From: <ccote_msl_at_yahoo.com>
Date: 14 Mar 2005 14:34:54 -0800
Message-ID: <1110839694.835163.171530_at_l41g2000cwc.googlegroups.com>
[Quoted] HI, I have an XMLType column that ha the following XML structure:
<Item id = "03" Description = "XXXXX" </Item>
</Report>
<Item id = "03" Description = "OPQRST" </Item>
</Report>
</Client>
TABLE(xmlsequence(extract(VALUE(x), '/Client/Report'))) d, TABLE(xmlsequence(extract(VALUE(x), '/Client/Report/Item'))) e
Date: 14 Mar 2005 14:34:54 -0800
Message-ID: <1110839694.835163.171530_at_l41g2000cwc.googlegroups.com>
[Quoted] HI, I have an XMLType column that ha the following XML structure:
<Client>
<Report id = "01"><Item id = "02" Description = "AAAAA;yuk" </Item>
<Item id = "01" Description = ",a.mdfbnelt;yuk" </Item>
<Item id = "03" Description = "XXXXX" </Item>
</Report>
<Report id = "02"><Item id = "02" Description = "JHIKLM" </Item>
<Item id = "01" Description = ",ABCDEF" </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) DescriptionFROM 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
Received on Mon Mar 14 2005 - 23:34:54 CET