Dump postgresql table to XML file

Try this, it seems the syntax is wrong, which you tried.

COPY (SELECT table_to_xml('table', true, false, '')) to 'C:/users/me/file.xml';

Alternative, you can try:

psql -p5432 db_name \copy (SELECT table_to_xml('table', true, false, '')) TO 'C:/users/me/file.xml';

OK so update. After spending the majority of my day struggling with this I have come up with a partial solution. Using information from here and Permission denied when trying to import a CSV file from PGAdmin I was able to get a command to work in PGAdmin 4 sort of...

COPY (SELECT table_to_xml('table_name', true, false, '')) to 'C:\XML\Table.xml';

The above command when run in PGAdmin will apparently do something I only assume is what I want. I have not had time to check if it actually creates a working XML file of the table if I use a smaller dataset. Yet if I follow the instructions in the before mentioned link and create a folder with a blank XML file in it and set the permissions to allow everyone to read/write to the folder the command executes. However it uses an obscene amount of memory. To write a table which is only around 2GB in size to XML the program consumed all 36GB of my memory before crashing itself. I don't have time to continue testing this, because I found that I could get to where I needed to be using a dump to a text file. anyway I hope this helps someone.

The copy-statement doesn't work for me - runs out of RAM.
Exporting with C# works fine, though (this still loads all data into memory):

public static void DataToXML()
{
    Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
    csb.Database = "YourDB";
    csb.Host = "localhost";
    csb.Port = 5432;
    csb.IntegratedSecurity = true;
    csb.Username = "postgres";
    // csb.Password = "";


    using (System.Data.DataTable dt = new System.Data.DataTable())
    {

        dt.TableName = "record";

        using (System.Data.DataSet ds = new System.Data.DataSet("geoip_blocks_temp"))
        {
            ds.Tables.Add(dt);
            // dt.Namespace = "foo";

            using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
            {
                con.ConnectionString = csb.ConnectionString;

                using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM geoip.geoip_blocks_temp";

                    using (System.Data.Common.DbDataAdapter da = Npgsql.NpgsqlFactory.Instance.CreateDataAdapter())
                    {
                        da.SelectCommand = cmd;

                        if (con.State != System.Data.ConnectionState.Open)
                            con.Open();

                        da.Fill(dt);

                        if (con.State != System.Data.ConnectionState.Open)
                            con.Close();
                    } // End Using da 

                } // End Using cmd 

            } // End Using con 


            //using (System.IO.Stream fs = System.IO.File.OpenWrite(@"D:\geoip_blocks_temp.xml"))
            //{
            //    using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
            //    {
            //        // System.IO.StringWriter sw = new System.IO.StringWriter();
            //        // dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
            //        dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
            //    } // End Using sw 

            //} // End Using fs 


            System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
            xs.Indent = true;
            xs.IndentChars = "    ";
            xs.NewLineChars = System.Environment.NewLine;
            xs.OmitXmlDeclaration = false;
            // xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql 
            xs.Encoding = new System.Text.UTF8Encoding(false);

            // <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
            using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(@"D:\geoip_blocks_temp.xml", xs))
            {
                dt.WriteXml(writer, System.Data.XmlWriteMode.IgnoreSchema);
            }

            System.Console.WriteLine(dt.Rows.Count);
        } // End Using ds 

    } // End Using dt 

} // End Sub DataToXML 

Or more memory-conserving:

public static void LargeDataToXML()
{
    string table_schema = "geoip";
    string table_name = "geoip_blocks_temp";

    // table_schema = "public";
    // table_name = "t_sys_language_monthnames";


    System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
    xs.Indent = true;
    xs.IndentChars = "    ";
    xs.NewLineChars = System.Environment.NewLine;
    xs.OmitXmlDeclaration = false; // // <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    // xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql 
    xs.Encoding = new System.Text.UTF8Encoding(false);

    string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");

    using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
    {
        writer.WriteStartDocument();
        writer.WriteStartElement(table_name);

        writer.WriteAttributeString("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
        // writer.WriteAttributeString("xsi", "schemaLocation", null, System.Xml.Schema.XmlSchema.InstanceNamespace);



        using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
        {
            con.ConnectionString = GetCS();

            using (System.Data.Common.DbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;


                if (con.State != System.Data.ConnectionState.Open)
                    con.Open();

                using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
                {

                    if (dr.HasRows)
                    {
                        int fc = dr.FieldCount;

                        string[] columnNames = new string[fc];
                        // System.Type[] columnTypes = new System.Type[fc];

                        for (int i = 0; i < dr.FieldCount; ++i)
                        {
                            columnNames[i] = dr.GetName(i);
                            // columnTypes[i] = dr.GetFieldType(i);
                        } // Next i 

                        while (dr.Read())
                        {
                            // object[] thisRow = new object[dr.FieldCount];

                            writer.WriteStartElement("record");

                            for (int i = 0; i < fc; ++i)
                            {
                                writer.WriteStartElement(columnNames[i]);
                                object obj = dr.GetValue(i);

                                if (obj != System.DBNull.Value)
                                {
                                    writer.WriteValue(obj);
                                }
                                else
                                    writer.WriteAttributeString("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true");

                                writer.WriteEndElement();
                            } // Next i

                            writer.WriteEndElement();

                        } // Whend 

                    } // End if (dr.HasRows) 

                } // End Using dr 

                if (con.State != System.Data.ConnectionState.Open)
                    con.Close();
            } // End Using cmd 

        } // End Using con 

        writer.WriteEndElement();
    } // ENd Using writer 

} // End Sub LargeDataToXML