10 Replies Latest reply: May 9, 2012 10:03 AM by user7955917 RSS

    search the xml node by attributes and display the entire row

    user7955917
      I am having a table with two columns say
      school_name string type
      student_notebookprice xmldata
      how do i search with oracle query having student_id=102 and schoolname="abc_school" with notebook price 20, how do i get only one single row
      abc_school,
      <students>
           <student id="101">
           <notebookprice>12</notebookprice>
           </student>
           <student id="102">
                <notebookprice>20</notebookprice>
                <notebookprice>30</notebookprice>
                <notebookprice>40</notebookprice>
      </student>
      </students>

      def_school,
      <students>
           <student id="301">
           <notebookprice>10</notebookprice>
           </student>
           <student id="302">
                <notebookprice>15</notebookprice>
                <notebookprice>16</notebookprice>
                
      </student>
      </students>
        • 1. Re: search the xml node by attributes and display the entire row
          odie_63
          Always give your database version : SELECT * FROM v$version.
          I am having a table with two columns say
          school_name string type
          student_notebookprice xmldata
          Why not give the real datatypes?

          Assuming STUDENT_NOTEBOOKPRICE is of XMLType datatype and version >= 11.1, you can use XMLExists to filter on XML data :
          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"
              )
          ;
          • 3. Re: search the xml node by attributes and display the entire row
            user7955917
            oracle version is 10.2 and ur assumption is correct it is xmltype
            • 4. Re: search the xml node by attributes and display the entire row
              odie_63
              user7955917 wrote:
              oracle version is 10.2
              This still isn't a version...
              It's probably too hard to issue "SELECT * FROM v$version"? ;)

              Seriously, the reason we always ask the exact version is because the optimal solution greatly depends on it.
              XML features have evolved quite a bit since Oracle 9 and some methods that were once the standard are now deprecated or less optimized than the new ones.

              Prior to release 11.1, you can use existsNode() function instead :
              select t.*
              from your_table t
              where school_name = 'abc_school'
              and existsNode(
                    t.student_notebookprice
                  , '/students/student[@id=102 and notebookprice=30]'
                  ) = 1
              ;
              • 5. Re: search the xml node by attributes and display the entire row
                user7955917
                Thank you very much . is it possible to display the row like this by using extractvalue function etc ..
                abc_school , 102, 30

                as the notebookprice tag has repeated values it is throwing two many values for a node error ...
                • 6. Re: search the xml node by attributes and display the entire row
                  odie_63
                  You mean as you asked here : {thread:id=2385928} ?
                  • 7. Re: search the xml node by attributes and display the entire row
                    user7955917
                    yes . what i want based on search criteria i need to display that row . initially i have tried with the below query

                    SELECT school_name,
                    extractvalue(value(x), '//id') as student_id ,
                    extractValue(value(x), '//teacher') AS teacher
                    FROM school t ,
                    TABLE( XMLSequence( Extract(t.obj_xml, '/students/student[id=101 and teacher="abc"]'))) x
                    where existsNode(t.obj_xml, '/students/student[id=101 and teacher="abc"]') = 1

                    but when i want to add the second student in the search , i am failed to produce the second student information in the output and i can able to add it to where clause and but how to display the student info in the output . 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 . will it cause any performence issue .

                    <students>
                    <student >
                    <id>101</id>
                    <teacher>abc</teacher>
                    </student>
                    <student >
                    <id>102</id>
                    <teacher>xyz</teacher>
                    <teacher>onp</teacher>
                    <teacher>rsm</teacher>
                    </student>
                    </students>


                    SELECT school_name,
                    extractvalue(value(x), '//id') as student_id ,
                    extractValue(value(x), '//teacher') AS teacher
                    FROM school t ,
                    TABLE( XMLSequence( Extract(t.obj_xml, '/students/student[id=101 and teacher="abc"]'))) x
                    where existsNode(t.obj_xml, '/students/student[id=101 and teacher="abc"]') = 1
                    and existsnode(t.obj_xml, '/students/student[id=102 and teacher="xyz"]') = 1
                    • 8. Re: search the xml node by attributes and display the entire row
                      odie_63
                      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.

                      If you want to filter on multiple ids :
                      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' )
                      ;
                      When the document is stored as XMLType, filter predicates are pushed back and applied on the base table instead of the resulting view.
                      • 9. Re: search the xml node by attributes and display the entire row
                        user7955917
                        can you please explain what does " path '.' " significance in the query and also i would be glad if u can suggest any book for oracle xml
                        • 10. Re: search the xml node by attributes and display the entire row
                          user7955917
                          ok after ready xpath and xquery . i have understood that "." significance that Selects the current node