This discussion is archived
3 Replies Latest reply: Jul 4, 2013 2:47 AM by odie_63 RSS

Help with XMLTABLE & XMLTYPE & PATH

bookert Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points