Home » Developer & Programmer » JDeveloper, Java & XML » XMLSequence and attribute condition
XMLSequence and attribute condition [message #290607] Sun, 30 December 2007 09:29 Go to next message
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.
Re: XMLSequence and attribute condition [message #290610 is a reply to message #290607] Sun, 30 December 2007 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: XMLSequence and attribute condition [message #290767 is a reply to message #290690] Mon, 31 December 2007 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
VAL is the name of my column in my table T (your table name was too long and too difficult to type).
Change it to your own.

Regards
Michel

[Updated on: Mon, 31 December 2007 10:42]

Report message to a moderator

icon14.gif  Re: XMLSequence and attribute condition [message #290820 is a reply to message #290607] Tue, 01 January 2008 01:45 Go to previous message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Thanks a lot Michel.
It's work for me.

Best Regards,
SAM.
Previous Topic: Registering XML schema in XE 10g
Next Topic: DISTINCT in XMLQuery ??
Goto Forum:
  


Current Time: Sat Dec 10 01:25:18 CST 2016

Total time taken to generate the page: 0.05089 seconds