473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

XML joining Nested resultsets

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

Jul 19 '05 #1
1 2944

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Top Gun | last post by:
In order to avoid multiple trips to the database, I would like to fill several tables in a DataSet with a single call to a stored procedure that will return resultsets for the appropriate tables. ...
0
by: Palle Girgensohn | last post by:
Hi! A performance question: I have some tables: Tabell "public.person" Kolumn | Typ | Modifierare ------------------+--------------------------+---------------...
1
by: Frank Rizzo | last post by:
I have a stored proc that returns 5 resultsets. How can I read each resultset into a separate data reader? Thanks
1
by: Steve C | last post by:
Hi, I'm having problems constructing a nested join. It's quite complex, so here's a simplfied example of the problem. Any thoughts on what I'm doig wrong - or if I've got the whole approach...
0
by: Crazy Cat | last post by:
Hi, Using Visual Basic 2005 -- I have a stored procedure that returns multiple resultsets. I fill a datareader (SQLDataReader to be exact) with the results from a command object's ExecuteReader...
5
by: praveen | last post by:
Hi, I am using nested resultsets to execute queries on two different tables. The code structure is: stmt1 = conn.createStatement(); rs1 = stmt1.executeQuery(query1); while (rs1.next()) {...
6
by: iKiLL | last post by:
Hi all I am developing in C#, CF2 and SQL Mobile. Currently my app is using Merge Replication. This is all working well. I have now decided to try and use Result sets in my application but I...
10
by: =?Utf-8?B?UGV0ZXI=?= | last post by:
I'm using this coding to get 2 resultsets thru datareader and then load them into 2 datatables and bind the datatables to datagridviews. But sdrGrid.NextResult() is returning false for some...
0
by: Camfa | last post by:
Hi all, I’ve been messing around with the following question for 2 days now, and i still haven’t find a fix. So i hope you can help me. I’ll quickly show you guys the situation: There are 2...
3
by: nannu | last post by:
Hi, i am using the following query string query = "SELECT *"+ "FROM DataSet"+ "WHERE Param_ID =(Select Data1 from testcases where TestCaseID='TC001') DataAdapter da=new DataAdapter();...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.