Importing multiple XML files from folder structure

2019-08-27 14:07发布

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" &lt;sip:14256781713@sip.ca.bonco.com:5030&gt;</NAME>
    <AUDIO>G711(ulaw)</AUDIO>
  </CALLER>
  <CALLEE>
    <IPADDR>192.168.1.195:8550</IPADDR>
    <NAME>&lt;sip:2195272326@sip.ca.bonco.com:5030&gt;</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 Recorder\2018\08\24\20180824181025-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.

0条回答
登录 后发表回答