Postgresql – Postgres: turn off formating for xml export

postgresqlserialization

I want to export some table to xml file:

\o output.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');

However, I'm struggling with psql formatting. The best I managed to get is:

\t on
\x on
\pset format unaligned
\o modified_records.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');
\o

Which results in 

table_to_xml|<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

<modified_records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tag1>val1</tag1>
  <tag2>val2</tag2>
</modified_records>

So I still have to remove table_to_xml|. Which is not hard:

cat modified_records.xml | sed '1s/^table_to_xml|//' 

# or even in psql:
\o | sed '1s/^table_to_xml|//' > modified_records.xml

but feels like fighting with Postgres.

Is there a way to dump just the value?

(there are plenty of options for \pset, but I can't find its docs)

Best Answer

The psql command \? shows the option to turn off expanded formatting:

Formatting:
...
  \x [on|off|auto]       toggle expanded output (currently off)

Typically it will show table data in one record per line if its "off" or else each field in its own line when switched on, e.g.:

postgres=# \x off
Expanded display is off.
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | en_IN   | en_IN |

When its switched on:

postgres=# \x on
Expanded display is on.
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_IN
Ctype             | en_IN
Access privileges |
-[ RECORD 2 ]-----+----------------------

With xml output, since everything is in one stream, it shows the record label before printing out the xml. The difference between /x on and /x off is as follows:

/x on

table_to_xml|<pg_attribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <attrelid>1255</attrelid>
  <attname>proname</attname>
  <atttypid>19</atttypid>
  <attstattarget>-1</attstattarget>

With /x off the output is:

/x off
<pg_attribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <attrelid>1255</attrelid>
  <attname>proname</attname>
  <atttypid>19</atttypid>
  <attstattarget>-1</attstattarget>

So before writing to the file you should change the commands issued as follows:

\t on
\x off
\pset format unaligned
Related Question