title: How to remove an extra char which is coming in XMLAGG() output
body: <p>Im using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production</p>
  
  <p>We replaced LISTAGG() with XMLAGG() to avoid concatenation error.
  when i check the lenght of charecters from both of the fuction output, XMLAGG() giving an extra char in length.
  Could you please suggest me how can i overcome this issue.</p>
  
  <p>Please find the below sql and out put</p>
  
  <p>XMLAGG():</p>
  
  <pre><code>SELECT TO_CHAR (
        SUBSTR (
           XMLAGG (XMLELEMENT (e, table_name, CHR (13)).EXTRACT (
                      '//text()') ORDER BY tablespace_name).GetClobVal (),
           1,
           2000))
        AS str_concate,
     LENGTH (
        TO_CHAR (
           SUBSTR (
              XMLAGG (XMLELEMENT (e, table_name, CHR (13)).EXTRACT (
                         '//text()') ORDER BY tablespace_name).GetClobVal (),
              1,
              2000)))
        AS str_length
  FROM all_tables
  WHERE table_name = 'TEST_LOAD
  </code></pre>
  
  <p>OUTPUT:</p>
  
  <pre><code>STR_CONCATE                  STR_LENGTH
  
  TEST_LOAD  TEST_LOAD              26
  </code></pre>
  
  <p>LISTAGG()</p>
  
  <pre><code>SELECT LISTAGG (SUBSTR (table_name, 1, 2000), CHR (13))
        WITHIN GROUP (ORDER BY tablespace_name)
        AS str_concate,
     LENGTH (
        LISTAGG (SUBSTR (table_name, 1, 2000), CHR (13))
           WITHIN GROUP (ORDER BY tablespace_name))
        AS str_length
  FROM all_tables
  WHERE table_name = 'TEST_LOAD';
  </code></pre>
  
  <p>OUTPUT:</p>
  
  <pre><code>STR_CONCATE                  STR_LENGTH
  
  TEST_LOAD TEST_LOAD               25
  </code></pre>