Database Software

Hey XDBers,

Just upgraded my development instance to 11g on Windows 2003 R2 and was playing around with some new code...

Ok... anyone know what is wrong with this picture? (sorry about the formatting)

select
xmlelement( "master",
xmlattributes( 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi" ),
xmlelement( "slaves",
xmlattributes( 'true' as "xsi:nil" )
) -- slaves
).getClobVal() xml
from dual;

All good - returns

<master xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<slaves xsi:nil="true"></slaves>
</master>

Okay - lets add some slave elements

select xmlelement( "master",
xmlattributes( 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi" ),
xmlelement( "slaves",
(
select xmlagg(
xmlelement( "slave",
xmlelement( "name", s.slave_name )
) -- slave
) -- xmlagg
from (
select 'Spartacus' slave_name
from dual
union all
select 'Gladiator' slave_name
from dual
) s
where 1 = 1
)
) -- slaves
).getClobVal() xml -- master
from dual;

Also all good - returns

<master xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<slaves>
<slave><name>Spartacus</name></slave>
<slave><name>Gladiator</name></slave>
</slaves>
</master>

However.....

select xmlelement( "master",
xmlattributes( 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi" ),
xmlelement( "slaves",
nvl(
(
select xmlagg(
xmlelement( "slave",
xmlelement( "name", s.slave_name )
) -- slave
) -- xmlagg
from (
select 'Spartacus' slave_name
from dual
union all
select 'Gladiator' slave_name
from dual
) s
where 1 = 1
),
xmlattributes( 'true' as "xsi:nil" )
) -- nvl
) -- slaves
).getClobVal() xml -- master
from dual;

Woops....

ORA-00907 : missing right parenthesis
(at the "as" in the xmlattributes expression)

What is the suggested workaround for these sorts of issues? How do I ensure that I can evaluate the subsidiary elements and if they do NOT exist simply emit the attribute xsi:nil="true"?

Any ideas?

Thanks,

Rhodry