SQL & PL/SQL

My problem is kind of complex so I'll try to reduce it to a more manageable example:

drop type msm force;
drop type list_msm force;
create type msm as object(
nume char(20)
);
/
create type list_msm as table of msm;
/

select xmlserialize (document xmlelement("values",
cast(multiset(
select trim(p.den) from produse p) as list_msm
)
) as clob indent size=2
) as "xml"
from dual;

This is where it gets tricky.

p.den is a field that contains spaces. I use trim(p.den) to remove them but because I cast this to list_msm, the values are then again mapped into msm objects, which have the "nume" field of char(20). So.. when generating the XML, the output still contains spaces.

This is an excerpt of the result:

<values>
<LIST_MSM>
<MSM>
<NUME>Prod.1 </NUME>
</MSM>
<MSM>
<NUME>Prod.2 </NUME>
</MSM>
<MSM>
<NUME>Prod.3 </NUME>
</MSM>
...
<Later.. I see that the forum removes the extra spaces but I ensure you that after eg. "Prod.1" there are a lot of spaces, as the original field from the "produse" table is defined as char(20))

So.. Is there a way to generate the XML without those extra spaces? Basically, I want to be able to trim them before I send them to the xml serializer, but the problem is that i have no control over the whole casting thing ( oracle magic happening that automatically creates all the xmlelements needed, etc.)

Somehow, when the xml engine steps over the collection and accesses the fields, somehow, i need a trim there. Maybe some kind of accesor for the fields in the type can be declared so that the xml engine uses it instead of the field itself directly. The accesor would then return trim(field). Sorry for this mumbo jumbo gibberish but I want to be as clear as possible.

Edited by: user9229988 on May 10, 2012 9:24 AM

Edited by: user9229988 on May 10, 2012 9:25 AM

Edited by: user9229988 on May 10, 2012 9:38 AM