2 Replies Latest reply on Nov 1, 2012 11:02 AM by 789866

    xmltable query

      Hi ,
      I want to prepare a query on xmltype column as below.

      select t.* from inputxml ix ,
      XMLTable ('/base' passing ix.message
      FirstValue varchar2(100) PATH 'first',
      SecondValue varchar2(100) PATH 'second'
      )t ;

      Here is xml –

      Here inputxml is a table where message is column of xmltype.
      Now we are retrieving values from nodes using xmltable feature.
      My Question is how we can put condition on xmltable column such as –

      If value at xml element first is ‘a’ then put it as ‘a’ else ‘b’ in column FirstValue for all other cases.

      So that path table column First will always have values either ‘a’ or ‘b’ irrespective of what it is in xml.

        • 1. Re: xmltable query
          What's wrong with a simple CASE statement in the SELECT ?
          SQL> select case when t.FirstValue = 'a' then 'a'
            2              else 'b'
            3         end as FirstValue
            4       , t.SecondValue
            5  from inputxml ix
            6     , xmltable('/base' passing ix.message
            7         columns FirstValue  varchar2(100) path 'first'
            8               , SecondValue varchar2(100) path 'second'
            9       ) t
           10  ;
          ---------- --------------------------------------------------------------------------------
          a          s
          It can be done directly in XMLTable too, but it must be slower :
          select t.*
          from inputxml ix
             , xmltable('/base' passing ix.message
                 columns FirstValue  varchar2(100) path 'if (first="a") then "a" else "b"'
                       , SecondValue varchar2(100) path 'second'
               ) t
          • 2. Re: xmltable query