XMLForest with blank spaces as value

[ekobudy]

Hello guys,

I need help about XMLForest,
here is my code :

create table example (
field1 varchar2(10),
field2 varchar2(10),
field3 varchar2(10));

insert into example values (null,null,null);

select xmlforest(lpad(nvl(field1,''),10,' ')||lpad(nvl(field2,''),10,' ') as "concatf1f2") from example;

the result is:
XMLFOREST(LPAD(NVL(FIELD1,''),10,'')||LPAD(NVL(FIELD2,''),10,'')AS"CONCATF1F2")
-------------------------------------------------------------------------------



1 row selected.

what i want, the result must be like this:
<concatf1f2> </concatf1f2>
concatf1f2 node with 20 blank space value.

anyone could help me?
i'm very appriciate it.

Thanks.

 

[taupirho]

Try

select
replace(XMLFOREST(NVL(field1,lpad('X',10,'X')) ||
NVL(field2,lpad('X',10,'X')) concatf1f2 ),'X',' ')
from example

 

[hoinz]

another idea:

NULL and '' are the same,
and so nvl(NULL,'') is still NULL,
and lpad, applied to NULL, will still give NULL

Try a blank in between, 'nvl(NULL,' ') instead of nvl(NULL,'')

 

[ekobudy]

Thank you All,

You are really helpfull.


Regards,


ekobudy