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?

show the function or even better build db-fiddle - I cant reproduce the issue - with null as ' ' works for me — Vao Tsun
– Vao Tsun, Commented Mar 9, 2018 at 12:25
copy(select XMLSERIALIZE( content xmlelement(name test, query_to_xml('select * from tb2', false, true, '')) as text)) TO '/Users/TEMP/test.xml' WITH NULL AS ' ' I'm using XMLSERIALIZE to perform some string operations. and the output is also in 'text' type. — KTB
– KTB, Commented Mar 11, 2018 at 16:02
Here is the result: <test><row xmlns:xsi="w3.org/2001/XMLSchema-instance">\n <col3>1</col3>\n <col4>1</col4>\n</row>\n\n<row xmlns:xsi="w3.org/2001/XMLSchema-instance">\n <col3>2</col3>\n <col4>2</col4>\n</row>\n\n<row xmlns:xsi="w3.org/2001/XMLSchema-instance">\n <col3>3</col3>\n <col4>3</col4>\n</row>\n\n<row xmlns:xsi="w3.org/2001/XMLSchema-instance">\n <col3>4</col3>\n <col4>4</col4>\n</row>\n\n<row xmlns:xsi="w3.org/2001/XMLSchema-instance">\n <col3>5</col3>\n <col4>5</col4>\n</row>\n\n</test> — KTB
– KTB, Commented Mar 11, 2018 at 16:04

Vao Tsun · Accepted Answer · 2018-03-21 08:05:43Z

2

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>

"

edited Mar 21, 2018 at 8:05

answered Mar 11, 2018 at 21:12

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

KTB Over a year ago

But this will not write the result to a file?

Vao Tsun Over a year ago

I use to stdout to show the result. change to '/path/to/file' and you have it in file, or redirect psql to a file with proper formatting

KTB Over a year ago

That's what I did, used copy(select XMLSERIALIZE( content xmlelement(name test, query_to_xml('select * from tb2', false, true, '')) as text)) TO '/Users/TEMP/test.xml''. But the problem was having \n in the result.

Vao Tsun Over a year ago

I updated the answer demonstrating that copy destination does not change the result

medley56 Over a year ago

Is there a way to get rid of the double quotes "..." around the result? Also, there are double quotes around every xml string. That can't be valid can it?

|

Collectives™ on Stack Overflow

Postgresql, using Copy function to export results to XML file

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related