Postgresql, using Copy function to export results to XML file

Question

I have written some SQL functions which output XML results. Since the output size is big, I need to write them directly to a xml file. To this I'm using COPY function as follows:

COPY(SELECT generate_xml_file()) TO '/Users/TEMP/test.xml'  

But the problem with this is, then generated file has \n character at the end of every tag. I tried to use the following options to eliminate '\n' but it didn't work:

 COPY(SELECT generate_xml_file()) TO '/Users/TEMP/test.xml' WITH NULL AS ' '

Is there any facility provided in Postgresql to get this done?

Answer 1

the \n is for new line, not null. If you use default tsv mode - each new line is represented as \n to distinguish difference between new line and new row, eg:

t=# copy(select query_to_xml('select datname,null blah from pg_database limit 2',false,true,'')) to stdout;
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">\n  <datname>postgres</datname>\n</row>\n\n<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">\n  <datname>t</datname>\n</row>\n\n

So simply csv option will rescue you, eg:

t=# copy(select query_to_xml('select datname,null blah from pg_database limit 2',false,true,'')) to stdout csv;
"<row xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">
  <datname>postgres</datname>
</row>

<row xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">
  <datname>t</datname>
</row>

"

https://www.postgresql.org/docs/current/static/sql-copy.html

COPY TO will terminate each row with a Unix-style newline (“\n”).

and so on...

update

copying to stdout or file does not matter:

t=# copy(select query_to_xml('select datname,null blah from pg_database limit 2',false,true,'')) to '/tmp/sp.xml' csv;
COPY 1
Time: 0.481 ms
t=# \! cat /tmp/sp.xml
"<row xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">
  <datname>postgres</datname>
</row>

<row xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">
  <datname>t</datname>
</row>

"