Export Mysql table into xml file same format as mysqldump using Perl

Question

Using Perl how to export Mysql table into xml file the same format as mysqldump do with command like this:

mysqldump --xml -u root -proot dbname tablename >tablename.xml

I know some Perl modules like XML::TreePP easy to use but their problem when they load xml file they have to load the entire file into memory before parsing and if the file size is larger than the allowed process memory will crash.

If you're running out of memory parsing a file generated by `mysqldump`, you'll still run out of memory if you dump the same data in the same format using a different method. I think what you're *really* asking is, "How can I parse a large XML file without running out of memory?" — ThisSuitIsBlackNot, Dec 22 '14 at 21:48
See [“Out of memory” while parsing large (100 Mb) XML file using perl](http://stackoverflow.com/q/7293687/176646) for how to parse large XML files without loading their full contents into memory. — ThisSuitIsBlackNot, Dec 22 '14 at 21:56
It is not the issue of out of memory, I need to implement the backup and restore manually not through mysqldump — daliaessam, Dec 22 '14 at 22:12
Is there a particular reason you can't use `mysqldump`? As an alternative, you can use the [`SELECT ... INTO OUTFILE`](http://dev.mysql.com/doc/refman/5.7/en/select-into.html) syntax to dump your data in CSV format, for example, and restore it with [`LOAD DATA INFILE`](http://dev.mysql.com/doc/refman/5.7/en/load-data.html) — ThisSuitIsBlackNot, Dec 22 '14 at 22:24
@ThisSuitIsBlackNot CSV exports are ok, but when the data has commas, then usually people switch to TSVs(tab-separated values) and when the data also contains tabs, then some other form of serialization is needed. From this point of view, XML is superior, but.. it also comes with the drawbacks XMLs usually have(verbosity, etc). — wsdookadr, Jan 01 '15 at 10:51
@average A valid CSV surrounds fields containing commas (or optionally all fields) in quotation marks, so there is no ambiguity. `SELECT ... INTO OUTFILE` and `LOAD DATA INFILE` both support this syntax, so there's no reason to switch to TSV or any other format. — ThisSuitIsBlackNot, Jan 01 '15 at 16:40

score 1 · Accepted Answer · edited May 23 '17 at 10:26

If you need to parse very large XML documents, there's a particular category of XML parsers, SAX(Simple API for XML) that functions as a stream parser and emits events while parsing the XML document. On CPAN you can find XML::LibXML::SAX (part of the XML::LibXML module on CPAN) which implements SAX and can be used to parse large XML documents :

Such an interface is useful if very large XML documents have to be 
processed and no DOM functions are required. By using this interface
it is possible to read data stored within an XML document directly
into the application data structures without loading the document
into memory.

You can read another SO thread about a similar problem where XML::Twig was recommended.

Export Mysql table into xml file same format as mysqldump using Perl

1 Answers1