conditional select when using xmltable to extract xml

lets say we have an xml as "<xml><emp>rohit</emp><emplang>spanish</emplang><other>other<other><other1><other1</other1></xml>"..this is just for eg...origininally i have a bigger xml wherein extraction of all xml tags is happening flawlessly using xmltable, xmlnamespaces , passing column syntaxes...

now my requirement is such that if xml data is having emplang as spanish , select all tag values except <other1>.
If it is other than spanish, select all tag values other than <other>

so, i need kind of conditional select query to extarct tags based upon a particular tag in the lot.
DevildibAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can't have the number of columns dynamically change based on the xml contents.

you could return NULL or some other dummy value for the <other> or <other1> elements depending on the language though.

with a simple case statement for each

for example,  if you used xmltable to extract each tag into a column of the same name
then try something like this...

case when emplang='spanish' then NULL else other1 end  as other1,  -- return NULL for spanish in the other1 column
case when emplang!='spanish' then NULL else other end as other,    -- return NULL for non-spanish in the other column

Open in new window

DevildibAuthor Commented:
when i write the select query as:
assuming the xml data is a clob column name say testcol in a table say test .Another column in the table is say DEPT which is varchar:

select x.* from test t,
xmltable(
xmlnamespaces('test namespaces' ...),
'/xml'
passing xmltype(t.testcol)
columns
EMP varchar2(20) path '//emp',
EMPLANG varchar2(20) path '//emplang',
case when EMPLANG = 'English' then
OTHER varchar2(20) path '//other' else OTHER1 varchar2(20) path '//other1' end)
x
where t.dept in ('SALES')

above query is giving me error :
ora-00907  : missing right paranthesis at line where case when is introduced.

Why is this happening?
sdstuberCommented:
no, you must generate the columns first in xmltable,  then in the select results use the case

something like this

SELECT emp, emplang, CASE WHEN emplang = 'English' THEN other ELSE other1 END AS other
  FROM test t, XMLTABLE(xmlnamespaces('test namespaces' ...),                      
 '/xml'
 passing xmltype(t.testcol)
 columns
 EMP varchar2(20) path '//emp',
 EMPLANG varchar2(20) path '//emplang',
 OTHER varchar2(20) path '//other' ,
 OTHER1 varchar2(20) path '//other1' )
 x
 where t.dept in ('SALES')
sdstuberCommented:
if you can post a complete xml with expected results I can post a full working example, not just pseudocode

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DevildibAuthor Commented:
Thanks...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.