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
