3 Replies Latest reply on Dec 22, 2014 8:51 AM by Dent, Arthur Dent

    SQLDeveloper 4.0.3 not displaying the output from an xmlquery

    Dent, Arthur Dent

      I have been tearing out what grey hair I have left over this...

       

      I am using the following:-

       

      SQL Developer 4.0.3.16

      Oracle DB 11.2.0.3

       

      I am trying to use XMLQuery but I only ever get null from any query when I run it in SQLDeveloper, running the same query in SQLPlus works as expected

       

      A simple example follows.....

      -- table

      create table xml_test(id number, xml_doc xmltype);

       

      --some data

      insert into xml_test values

      (1,

      xmltype('<COLLECTION>

         <ARTIST>

            <NAME>Porcupine Tree</NAME>

            <ALBUMS>

               <ALBUM>

                  <TITLE>On The Sunday of Life</TITLE>

               </ALBUM>

               <ALBUM>

                  <TITLE>In Absentia</TITLE>

               </ALBUM>

            </ALBUMS>

         </ARTIST>

         <ARTIST>

            <NAME>Peter Hammill</NAME>

            <ALBUMS>

               <ALBUM>

                  <TITLE>Nadir''s Big Chance</TITLE>

               </ALBUM>

            </ALBUMS>

         </ARTIST>

      </COLLECTION>'));

       

      -- the query

      select id, xmlquery('for $i in /COLLECTION/ARTIST

                           where $i /NAME = "Peter Hammill"

                           return $i/ALBUMS/ALBUM'

                           passing by value xml_doc returning content) albums

      from xml_test;

       

      in SQLDeveloper I get 1, null

       

      However, it works in SQLPlus

       

             ID

      ----------

      ALBUMS

      ------------------------------------------

               1

      <ALBUM>

        <TITLE>Nadir&apos;s Big Chance</TITLE>

      </ALBUM>

       

      Help.

       

      The other Oracle developers in the office just cringe and find rapidly find something else to do whenever anyone raises a question about XML.  Is it just us or is this common?