Postgres Pro
Downloads
Home   >   mailing lists

Thread: xmlElement and \n

xmlElement and \n

From
Enrico Oliva
Date:
Hi, I  would like to have a output xml file with the carriage return at the end of each element.

COPY(

select xmlelement(name "IDXS",XMLAGG(xml_task_group))
from (select i.relname as IDX,
    xmlelement(name "IDX",XMLATTRIBUTES(i.relname as "name"),   
    xmlelement(name "IDX_IS_UNIQUE",CASE WHEN  ix.indisunique  THEN 'Yes' ELSE 'No' END ),
    xmlelement(name "IDX_IS_PRIMARY_KEY",CASE WHEN ix.indisprimary THEN 'Yes' ELSE 'No' END),        
    xmlelement(name "IDX_COLUMNS",xmlagg(xmlelement(name "IDX_COLUMN",a.attname)))) as xml_task_group
from
 ....

)  TO 'proteoIndes.xml'

The result is in only one text raw:
<IDXS><IDX name="as001_c01pk"><IDX_IS_UNIQUE>Yes</IDX_IS_UNIQUE><IDX_IS_PRIMARY_KEY>Yes</IDX_IS_PRIMARY_KEY><IDX_COLUMNS><IDX_COLUMN>as001_id</IDX_COLUMN>...

I would like to have
<IDXS>
      <IDX name="as001_c01pk">
      <IDX_IS_UNIQUE>Yes</IDX_IS_UNIQUE>
...



Enrico

Re: xmlElement and \n

From
Luca Ferrari
Date:
On Thu, Sep 19, 2013 at 9:46 AM, Enrico Oliva <oliva.enrico@gmail.com> wrote:

> I would like to have
> <IDXS>
>       <IDX name="as001_c01pk">
>       <IDX_IS_UNIQUE>Yes</IDX_IS_UNIQUE>
> ...

I'm not sure there is an indenting facility, since indentation
strongly depends on what the user think about.
However you can concatenate a new line at each xmlelement call:

SELECT xmlelement(name foo, xmlattributes(current_date as bar),
'cont', 'ent') || chr(10) || 'hello world'

Luca