Importing multiple XML files from folder structure into MS Access
Importing multiple XML files from folder structure into MS Access
I've a small office phone calls DB, saved into multilevel enclosed folders structure by year, month, date (i.e. folder 2017 contains month folders 1,2,3...12, each of those contains day folders 1,2,3...30,31). Each day folder contains multiple pairs of XML and WAV files (named like this: 20180824181025-0000-P7D53Z.xml, 20180824181025-0000-P7D53Z.wav), each pair is a recording of a single phone call. All XML files have the same structure:
<?xml version="1.0" encoding="ISO-8859-1"?>
<CALL>
<ID>20180824181025-0000-P7D53Z</ID>
<SIP-ID>1be206584b8130a605a71b867f990e8d@82.212.65.23</SIP-ID>
<SUCCEED>true</SUCCEED>
<CALLER>
<IPADDR>82.212.65.23:5030</IPADDR>
<NAME>"4256781713" <sip:14256781713@sip.ca.bonco.com:5030></NAME>
<AUDIO>G711(ulaw)</AUDIO>
</CALLER>
<CALLEE>
<IPADDR>192.168.1.195:8550</IPADDR>
<NAME><sip:2195272326@sip.ca.bonco.com:5030></NAME>
<AUDIO>G711(ulaw)</AUDIO>
</CALLEE>
<TIME>
<INIT>2018-08-24 18:20:25</INIT>
<BEGIN>2018-08-24 18:20:38</BEGIN>
<END>2018-08-24 18:20:47</END>
<DURATION>9</DURATION>
</TIME>
<RECORD>
<ROOT>K:Phone Recorder</ROOT>
<PATH>K:Phone Recorder201882420180824181025-0000-P7D53Z.wav</PATH>
<FILENUM>1</FILENUM>
</RECORD>
<DTMF/>
</CALL>
I tried to manually import file by file to Access, but the appended tables have no linking keys, as the source XML is not structured for relational database. How would I automate content import from such multilevel folder structure into MS Access 2016 Calls DB by using an XSLT transform, and VBA folder iteration code?
The goals is to view a flat calls list, sorted by year / month / date / time, with separators for each year and month. Each record should show parties phone numbers, date&time in/out, call duration, and a linked WAV file, clicking on it should open a default audio player to listen to the recording. I couldn't find any similar question or solution here on importing multiple XML files to Access from enclosed folder structure.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
