3 Replies Latest reply: Jul 4, 2013 4:47 AM by odie_63 RSS

    Help with XMLTABLE & XMLTYPE & PATH

    bookert

      Hi xml gurus,

       

      Please help me with the following:

       

      I have very limited experience with xml. I have a query on one database that is behaving differenlty on another database.

       

      My test database is Oracle 11g version 11.2.0.3

      My production database is Oracle 11g version 11.2.0.3

       

      I have a table that contains xmltype data column called for example "myxml_column_table" that has two columns, 'REQUEST_ID' of number data type and 'MESSAGE_BODY' of xmltype. The message_body contains data looking similar to the following xml file:

       

      <ROOTPATH>

        <HEADER>

          <HeaderControlRef>1001</HeaderControlRef>

          <MESSAGE_HEADER>

         <MessageNumber>123456</MessageNumber>

          </MESSAGE_HEADER> 

        </HEADER>

      </ROOTPATH>

       

      The example query:

       

      SELECT t.request_id     as request_id

            ,int_header.a     as HeaderControlRef

            ,message_header.a as MessageNumber

        FROM ( SELECT request_id

                     ,message_body

                 FROM myxml_column_table

                WHERE request_id  = 1 ) t

      INNER JOIN        

      XMLTABLE( 'for $i in /ROOTPATH/HEADER

                  return $i' passing t.message_body

                  columns a varchar2(250) path 'HeaderControlRef'

                         ,message_header xmltype path 'MESSAGE_HEADER'

                ) int_header on 1=1

      LEFT OUTER JOIN

      XMLTABLE( 'for $i in /MESSAGE_HEADER

                  return $i' passing int_header.message_header

                  columns a varchar2(250) path 'MessageNumber'

                ) message_header on 1=1

        WHERE int_header.a = 1001;

       

      The problem I have is that my MessageNumber is being returned as null on the production database, but returns 123456 from my test database as expected. Any help would be appreciated on this.

        • 1. Re: Help with XMLTABLE & XMLTYPE & PATH
          odie_63

          Hi,

           

          Please post full test case from both environments showing what you've observed (use SQL*Plus and copy/paste everything here, including explain plans).

           

          Thanks.

          • 2. Re: Help with XMLTABLE & XMLTYPE & PATH
            bookert

            Hi odie_63,

             

            Thank you for your response.

             

            I have given the file and query as just an example of what is happening, It is not allowed for me to post the full test case and explain plans on this forum. Also I don't have access to sql*plus, I only have sqldeveloper.

             

            One thing I forgot to also mention is that the DB's are on different servers running Red Hat Enterprise Linux Server release 5.9 (Tikanga).


            • 3. Re: Help with XMLTABLE & XMLTYPE & PATH
              odie_63

              It is not allowed for me to post the full test case and explain plans on this forum. Also I don't have access to sql*plus, I only have sqldeveloper.

              Then I'm afraid no one will help with such sketchy details.

              Both databases are the same version, same OS version, so I guess the problem is not so trivial to tackle, especially remotely, on a forum.

               

              I suggest you open a SR on My Oracle Support.