Skip to content

Instantly share code, notes, and snippets.

@atomotic
Last active February 10, 2024 13:43
Show Gist options
  • Save atomotic/8cdb9f233136eea2ad507bb6940c5c8e to your computer and use it in GitHub Desktop.
Save atomotic/8cdb9f233136eea2ad507bb6940c5c8e to your computer and use it in GitHub Desktop.
load xml files into SQLite and transform to json

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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment