asked on

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.

Sean Stuber

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

Devildib

ASKER

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?

Sean Stuber

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')

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Devildib

ASKER

Thanks...