load xml files into SQLite and transform to json

README.md

Install sqlpkg

Install extensions

sqlpkg install sqlite/fileio
sqlpkg install jakethaw/xmltojson

Start

$ sqlite3
.load /Users/raffaele/.sqlpkg/sqlite/fileio/fileio.dylib
.load /Users/raffaele/.sqlpkg/jakethaw/xmltojson/xmltojson.dylib

Load xml files and convert to json

Assuming you have a local directory named xml-data containing XML files, you can create a table and convert the XML data to JSON:

create table metadata as 
  select name as filename, 
         data as xml, 
         xml_to_json(data, 2) as json 
  from fsdir('xml-data');

Query json

Example query

select json->'$.?xml.mag:bib.dc:identifier' from metadata;