I am having a table with two columns sayWhy not give the real datatypes?
school_name string type
select t.* from your_table t where school_name = 'abc_school' and xmlexists( '/students/student[@id=$StudID and notebookprice=$NbPrice]' passing t.student_notebookprice , '102' as "StudID" , '30' as "NbPrice" ) ;
user7955917 wrote:This still isn't a version...
oracle version is 10.2
select t.* from your_table t where school_name = 'abc_school' and existsNode( t.student_notebookprice , '/students/student[@id=102 and notebookprice=30]' ) = 1 ;
The xmltable option that u have specified looks good but what i fear is , it produce multiple combination with all the tags in the xml with the other columns in the table .What you're using right now with TABLE( XMLSequence(...) ) just does the same.
When the document is stored as XMLType, filter predicates are pushed back and applied on the base table instead of the resulting view.
SELECT school_name , x1.id , x2.teacher FROM school t , XMLTable('/students/student' passing t.obj_xml columns id number path 'id' , teachers xmltype path 'teacher' ) x1 , XMLTable('/teacher' passing x1.teachers columns teacher varchar2(30) path '.' ) x2 WHERE ( x1.id = 101 AND x2.teacher = 'abc' ) OR ( x1.id = 102 AND x2.teacher = 'xyz' ) ;