2 Replies Latest reply: Nov 1, 2012 6:02 AM by 789866 RSS

    xmltable query

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

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

      Here is xml –
      <base>
      <first>a</first>
      <second>s</second>
      </base>

      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.

      Thanks,
      Shobhraj
        • 1. Re: xmltable query
          odie_63
          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  ;
           
          FIRSTVALUE SECONDVALUE
          ---------- --------------------------------------------------------------------------------
          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
            789866
            Thanks