How to avoid extra whitespace produced by Teradata's XMLCONCAT and XMLAGG

Question

When using XMLAGG or XMLCONCAT, it seems Teradata adds extra whitespace between the concatenated content:

with t (x) as (select 1)
select 
  xmlserialize(content xmlconcat(1, 2, 3) as varchar(1000)) a,
  xmlserialize(content xmlagg(v order by v) as varchar(1000)) b
from (
  select 1 from t
  union all
  select 2 from t
  union all
  select 3 from t
) as u (v)

The above produces:

|a    |b    |
|-----|-----|
|1 2 3|1 2 3|

Is there any way to avoid that extra whitespace artifact from XML concatenation and get this, instead?

|a  |b  |
|---|---|
|123|123|

Answer 1

An obvious hack would be to introduce an "impossible" sequence of characters during the concatenation, and remove that again from the result:

with t (x) as (select 1)
select 
  oreplace(
    xmlserialize(content xmlconcat(1, '##', 2, '##', 3) as varchar(1000)), 
    ' ## '
  ) a,
  oreplace(
    oreplace(
      xmlserialize(content xmlagg(trim(v || '##') order by v) as varchar(1000)),
      '## '
    ), '##'
  ) b
from (
  select 1 from t
  union all
  select 2 from t
  union all
  select 3 from t
) as u (v)

The result is now:

|a  |b  |
|---|---|
|123|123|