Inline output using query_to_xml in postgres

Question

Running following query in postgresql

select REPLACE(
          REPLACE(
             REPLACE(
                REPLACE(
                   query_to_xml(
                      'select 1 "col1",2 "col2",3 "col3"
                         union all
                         select 11 "col1",22 "col2",33 "col3"
                         union all
                         select 111 "col1",222 "col2",333 "col3"',
                      true,
                      false,
                      ''
                   )::text ,
                   '< row >',
                   '< Leaf >'
                ),
                '< /row >',
                '< /Leaf >'
             ),
             '< table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >',
             '< Tree >'
          ),
          '< /table >',
          '< /Tree >'
       )

Results in (multiple lines)

< Tree >

< Leaf >

< col1 >1< /col1 >

< col2 >2< /col2 >

< col3 >3< /col3 >

< /Leaf >

< Leaf >

< col1 >11< /col1 >

< col2 >22< /col2 >

< col3 >33< /col3 >

< /Leaf >

< Leaf >

< col1 >111< /col1 >

< col2 >222< /col2 >

< col3 >333< /col3 >

< /Leaf >

< /Tree >

Required in(i.e single line statement)

< Tree >  < Leaf >   < col1 >1< /col1 >   < col2 >2< /col2 >   < col3 >3< /col3 > < /Leaf >  < Leaf >   < col1 >11< /col1 >   < col2 >22< /col2 >   < col3 >33< /col3 > < /Leaf >  < Leaf >   < col1 >111< /col1 >   < col2 >222< /col2 >   < col3 >333< /col3 > < /Leaf >  < /Tree > 

Answer 1

You need to remove the newline character from the result. To remove new line you can use regexp_replace(column_or_result, E'[\\n\\r]+', ' ', 'g' ).

Your query should look like this:

select regexp_replace( REPLACE(REPLACE(REPLACE(REPLACE(query_to_xml('select 1 "col1",2 "col2",3 "col3" union all select 11 "col1",22 "col2",33 "col3" union all select 111 "col1",222 "col2",333 "col3"',true,false,'')::text ,'< row >','< Leaf >') ,'< /row >','< /Leaf >') ,'< table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >','< Tree >') ,'< /table >','< /Tree >'), E'[\n\r]+', ' ', 'g' )