OraFAQ Forum: JDeveloper, Java & XML » XMLSequence and attribute condition

Home » Developer & Programmer » JDeveloper, Java & XML » XMLSequence and attribute condition

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XMLSequence and attribute condition [message #290607]

Sun, 30 December 2007 09:29

sammeras
Messages: 28
Registered: September 2007
Location: Israel

Junior Member

Hello,
i hava a table contain XML string (CREATE TABLE tblControllerProjectsXML OF XMLType)
--The XML string in the table is:
<?xml version="1.0"?>
<Solution>
<Project ProjectID="5" Name="Unassigned TeamWork Project">
<Database MonitoredDBID="2" />
<Database MonitoredDBID="5" />
<Database MonitoredDBID="13" />
<Database MonitoredDBID="12" />
<Database MonitoredDBID="14" />
<Database MonitoredDBID="15" />
</Project>
<Project ProjectID="9" Name="Unassigned TeamWork Project">
<Database MonitoredDBID="9" />
<Database MonitoredDBID="10" />
<Database MonitoredDBID="15" />
</Project>
</Solution>

I tried a lot of queries, but none of them work well for me.
The query that i wrote:
--My Query
SELECT extractValue(value(d), '/Database/@MonitoredDBID') AS MonDBID,
extractValue(value(p), '/Project/@ProjectID') AS ProId
FROM tblControllerProjectsXML X,
TABLE(XMLSequence(extract(X.OBJECT_VALUE,'/Solution/Project/Database'))) d,
TABLE(XMLSequence(extract(X.OBJECT_VALUE,'/Solution/Project'))) p

I got strange results:
------------------
MonDBID ProId
2 5
2 9
5 5
5 9
13 5
13 9
12 5
12 9
14 5
14 9
15 5
15 9
9 5
9 9
10 5
10 9
15 5
15 9
18 rows selected.

It must returns me 9 results (5=> 2,5,13,12,14,15 AND 9=> 9,10,15) , not 2*9 results-(cartize).
How can i get results grouping by ProjectID attribute?
that's mean just 9 results (6 for ProjectID=5, 3 for ProjectID=9)

Thanks guys.

Report message to a moderator

Re: XMLSequence and attribute condition [message #290610 is a reply to message #290607]

Sun, 30 December 2007 10:59

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> col project format a10
SQL> col database format a10
SQL> select extractvalue(value(a),'/Project/@ProjectID') project,
  2         extractvalue(value(b),'/Database/@MonitoredDBID') database
  3  from t,
  4       table(xmlsequence(extract(val,'/Solution/Project'))) a,
  5       table(xmlsequence(extract(a.column_value,'/Project/Database'))) b
  6  /
PROJECT    DATABASE
---------- ----------
5          2
5          5
5          13
5          12
5          14
5          15
9          9
9          10
9          15

9 rows selected.

Regards
Michel

[Updated on: Sun, 30 December 2007 11:00]

Report message to a moderator

Re: XMLSequence and attribute condition [message #290690 is a reply to message #290610]

Mon, 31 December 2007 02:00

sammeras
Messages: 28
Registered: September 2007
Location: Israel

Junior Member

Thanks you very mutch for your helping Razz

,
But the code dosn't work for me.
I get error:
ERROR at line 4:
ORA-00904: "VAL": invalid identifier

Did the version is the reson?
Did you create the table as i created it (CREATE TABLE tblControllerProjectsXML OF XMLType) ?

I use Oracle 10g ver 10.1.0.2.0

Thanks Michel.

Regards,
Sam.

Report message to a moderator