XML joining Nested resultsets

From: <ccote_msl_at_yahoo.com>
Date: 15 Mar 2005 01:58:14 -0800
Message-ID: <1110880694.448331.170300@f14g2000cwb.googlegroups.com>

HI, I have an XMLType column that has 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(VALU­E(x), '/Client/Report'))) d, TABLE(xmlsequence(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 Received on Tue Mar 15 2005 - 03:58:14 CST