title: How to transform and stream large XML files to postgres ? Mediawiki dump postgres
body: <p>I want to use postgres' full text search on the largest public natural language corpus available. I downloaded this <a href="https://dumps.wikimedia.org/enwiki/20200201/enwiki-20200201-pages-articles-multistream14.xml-p7697599p7744799.bz2" rel="nofollow noreferrer">wikimedia dump stub</a> of a few megs for the example, the target is to work further with <a href="https://dumps.wikimedia.org/enwiki/20200201/" rel="nofollow noreferrer">dumps around 70GB uncompressed</a>. <a href="https://www.mediawiki.org/xml/export-0.10.xsd" rel="nofollow noreferrer">Here is the xsd</a>. 
  I know there are other <a href="http://www.statmt.org/moses/?n=Moses.LinksToCorpora" rel="nofollow noreferrer">open parallel corpora</a> easier to work with, I want to focus on wikimedia here.</p>
  
  <p>This might seem like a duplicate, but I would like to investigate a simpler approach compared to the other proposals I found : <a href="https://www.postgresql.org/message-id/5D5B211D-317B-4A62-B625-C2D28323DAFD%40gmx.net" rel="nofollow noreferrer">postgres mailing list and lo</a>, <a href="https://www.postgresql.org/message-id/flat/CANtp6R%2B4mm9HmrcVbSCCE_k3gXeCgnbuoL7UjibZoZJwoehrcg%40mail.gmail.com#5896eb38c601dbcc00549726a9d5e07c" rel="nofollow noreferrer">postgres mailing list and js</a>, <a href="https://stackoverflow.com/a/33211885/7359123">here with pg_read_file</a>, <a href="https://stackoverflow.com/a/34303681/7359123">here with nodejs</a>, <a href="https://stackoverflow.com/questions/49862106/importing-an-xml-schema-into-postgres-to-automatically-create-a-table-and-then-p">here with splitting</a>, <a href="https://stackoverflow.com/questions/54109811/how-to-import-big-xml-file-10gb-into-postgresql">here with splitting + csv</a>...</p>
  
  <p>I would like to <strong>preprocess</strong> the <strong>xml before entering postgres</strong>, and <strong>stream</strong> it <strong>with</strong> the <strong>copy</strong> command. <strong>BaseX</strong> allows serialization of xml to csv/text with commandline and xpath. I already have some <a href="http://ix.io/2csC" rel="nofollow noreferrer">stub xpath command</a> within postgres.</p>
  
  <p>The text tag in the XML encompasses huge text blobs, the wikipedia article contents in wikitext, and those are tricky to put into csv (quotes, double quotes, newlines + all the wikitext weird syntax) so I wonder about the format. I would ideally like a stream, thinking currently of :</p>
  
  <pre><code>basex [-xpath command] | psql -c 'COPY foo FROM stdin (format ??)'
  </code></pre>
  
  <p>Here is my <strong>question</strong> : can basex process the xml input, and output the transform in stream rather than in batch? If yes, what output format could I use to load into postgres?</p>
  
  <p>I intend to eventually have the data stored in <a href="https://www.mediawiki.org/wiki/Manual:Database_layout" rel="nofollow noreferrer">the mediawiki postgresql schema (at the bottom of the link)</a>, but I will fiddle with a toy schema with no index, no trigger... first.
  <a href="http://ix.io/2csA" rel="nofollow noreferrer">The problem of wikitext remains, but that's another story</a>.</p>