SQLite Forum

Importing XML
Login

Importing XML

(1) By anonymous on 2021-12-13 11:04:25 [link] [source]

Any pointers on importing an XML list of contacts exported from my phone into a SQLite database:

There are 5123 blocks looking like below and some blocks have additional fields like TEL;WORK:12345 or NOTE:This is some note text

<contact lookup="0r193-1612141412181224161214141218">BEGIN:VCARD
VERSION:2.1
N:;Name;;;
FN:Full Name
TEL;CELL:0123456789
END:VCARD
</contact>

As a casual SQLite user I was thinking of doing a bunch of find and replaces until I have just a list of SQL INSERTs for everything.

(2) By jake on 2021-12-13 12:41:12 in reply to 1 [link] [source]

If you are okay with using third party extension functions, then a possible solution might be to utilise the xml_to_json function combined with the built-in JSON functions e.g.

.load xml_to_json

.mode box
SELECT Max(CASE WHEN j2.value LIKE 'N%' THEN j2.value END) N,
       Max(CASE WHEN j2.value LIKE 'FN%' THEN j2.value END) FN,
       Max(CASE WHEN j2.value LIKE 'TEL%' THEN j2.value END) TEL
  FROM json_each(xml_to_json('<contact lookup="0r193-1612141412181224161214141218">BEGIN:VCARD
VERSION:2.1
N:;Name;;;
FN:Full Name
TEL;CELL:0123456789
END:VCARD
</contact>')) j
  JOIN json_each('["' || Replace(Trim(json_extract(j.value, '$.#text'), Char(10)), Char(10), '","') || '"]') j2
 GROUP BY j.value;
┌────────────┬──────────────┬─────────────────────┐
│     N      │      FN      │         TEL         │
├────────────┼──────────────┼─────────────────────┤
│ N:;Name;;; │ FN:Full Name │ TEL;CELL:0123456789 │
└────────────┴──────────────┴─────────────────────┘

If you have a directory of such XML files then you could also combine with the fsdir function e.g.

SELECT Max(CASE WHEN j2.value LIKE 'N%' THEN j2.value END) N,
       Max(CASE WHEN j2.value LIKE 'FN%' THEN j2.value END) FN,
       Max(CASE WHEN j2.value LIKE 'TEL%' THEN j2.value END) TEL
  FROM fsdir('.')  f
  JOIN json_each(xml_to_json(f.data)) j
  JOIN json_each('["' || Replace(Trim(json_extract(j.value, '$.#text'), Char(10)), Char(10), '","') || '"]') j2
 WHERE f.name LIKE '%.xml'
 GROUP BY
       f.name,
       j.value;
┌────────────┬──────────────┬─────────────────────┐
│     N      │      FN      │         TEL         │
├────────────┼──────────────┼─────────────────────┤
│ N:;Name;;; │ FN:Full Name │ TEL;CELL:0123456789 │
└────────────┴──────────────┴─────────────────────┘

(4) By anonymous on 2021-12-13 13:41:30 in reply to 2 [link] [source]

Jake, 
I think this is going to be perfect as I want to do the same with SMSs and probably redo with other family phones to filter / transfer / backup etc. using a SQLite database.

My searches also gave me a number of online conversion tools, I`m a bit hesitant to use them though, but I think Jake`s extension will be perfect.

Thank you both!

(3) By little-brother on 2021-12-13 12:42:22 in reply to 1 [source]

It looks like a VCard format. So the simplest way is to use any online converter e.g. https://www.aconvert.com/document/vcf-to-csv/ to convert your file to csv and then you can easily import it to SQLite database by CLI (or an another visual tool).

(5) By Simon Slavin (slavin) on 2021-12-13 13:49:27 in reply to 3 [link] [source]

I want to endorse this solution. It does mean you'll have a two-stage process but both of those stages are very frequently done, and there are good, flexible, tools to do them.

(6) By Ryan Smith (cuz) on 2021-12-13 14:08:21 in reply to 3 [link] [source]

Indeed, I agree with little-brother and Simon, with the small added mention that it seems to me your export is wrapped in XML, which isn't itself the standard way to do so, and so no real mainstream single-unroll method for all of it exists.

You have one standard (vCard) rolled into another standard (XML), so best is to use a two-pronged unroll the one (very standard xml) and then the second (very standard vCard) using any of the plentiful tools that exist for each.