trailing spaces in Oracle's xmlserializer

Question

I'm using XMLSerializer in an oracle sqlplus query for a linux job. On the SQLDeveloper command tab everything looks fine but on linux I see the xml output being trailed with space:

${ORAPLUS} -s /@tns_entry <<_ORALINE
--WHENEVER OSERROR  EXIT 1
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET TERM OFF
SET SHOW OFF
SET FEEDBACK OFF
SET HEADING OFF
SET HEAD OFF
SET PAGESIZE 0
set linesize 180
--set wrap on
set long 100000
SET LONGCHUNKSIZE 100000
SPOOL ${SPOOLFILE} APPEND
select xmlserialize(content xmlelement( "parent", xmlelement("child", 
       xmlelement("child-of-child", 'value'))) indent size=2) from dual;
SPOOL OFF
EXIT 0
_ORALINE

output:

<parent>                                                                                                                                                                     
  <child>                                             How do I get rid of the spaces here?                                                                                                                    
    <child-of-child>value</child-of-child>                                                                                                                                   
  </child>                                                                                                                                                                   
</parent> 

Of course I can a run post-pretty-up command to remove the trailing spaces but that seems like symptom fighting.

Answer 1

It isn't really the XMLSerialize() call that's doing this, it's the default behaviour for spooled data.

In your script, add:

SET TRIMSPOOL ON

From the SQL*Plus documentation:

SET TRIMS[POOL] {ON | OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF enables SQL*Plus to include trailing blanks.

There is an equivalent for interactive sessions, SET TEIMOUT ON, which is useful if you're redirecting output rather than spooling it.