postgresql - 在 postgres 中使用 query_to_xml 进行内联输出

Question

在 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 >'
       )

结果(多行)

< 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 >

必需的(即单行语句)

< 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

您需要从结果中删除换行符。要删除新行，您可以使用 regexp_replace(column_or_result, E'[\\n\\r]+', ' ', 'g' )。

您的查询应如下所示:

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' )