Case statement with xml construction
select
XMLType.GetClobVal(
XMLElement("variable"
,XMLAttributes('vcInternationalDate[]' as "type")
,XMLAgg(XMLElement("item"
CASE WHEN TEMPO IS NOT NULL THEN
, XMLElement("utc",TO_CHAR(TIME,'dd/MM/yyyy HH24:MI:SS')) END
, XMLElement("timezone_offSet", TIMEZONE)
)
)
)
)
as "CREATIONDATE"
from twgenericosdb.TEST
where ID = 1
The query above without the case statement produce the follow result :
<variable type="vcInternationalDate[]">
<item>
<utc>20/12/2009 15:00:00</utc>
<timezone_offSet>3600000</timezone_offSet>
</item>
</variable>
What i am looking for is a way in the case that utc is null the statement do not produce the utc tag's, because utc field is NULL the query returns the follow result
<variable type="vcInternationalDate[]">
<item>
<utc></utc>
<timezone_offSet>3600000</timezone_offSet>
</item>
</variable>
I'm trying to use the case statement to dont return the utc tag's in the case the UTC is NULL but i'm getting the error ORA-00917:missing comma.
Can any one help me ?
Thanks Very Much.