This discussion is archived
2 Replies Latest reply: Nov 1, 2012 4:02 AM by 789866 RSS

xmltable query

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

Legend

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