Hello everyone 
I have a large (90Mb) text file in xml format to import into Access. I can’t simply use the XML importer, because the xml is in this format:
If Access’s inbuilt xml importer is used, then ‘group’ tables are created with no foreign key linking them back to the pk in the ‘record’ table.
There are ~15k records, and both attributes and groups are optional and repeatable, so there’s no set pattern. My ‘solution’ is to import the file to string, manipulate to the file below, which can then obviously be imported as I’ve added the keys.
However the speed it’s running seems to indicate it will take a day to finish!
So:
1/ can anyone think /know of a better way to import xml into access and have the tables linked
2/ what is the way to deal with 100Mb strings optimally?
Current code, which I accept may be no good (although it works!)!:
Thanks in advance; any help appreciated!
I have a large (90Mb) text file in xml format to import into Access. I can’t simply use the XML importer, because the xml is in this format:
Code:
<record>
<recordid>id</recordid>
<attribute.1>value</attribute.1>
<attribute.n>value</attribute.n>
<group1...n>
<attribute1...n>value</attribute1>
</group>
</record>There are ~15k records, and both attributes and groups are optional and repeatable, so there’s no set pattern. My ‘solution’ is to import the file to string, manipulate to the file below, which can then obviously be imported as I’ve added the keys.
Code:
<record>
<recordid>id</recordid>
<attribute.1>value</attribute.1>
<attribute.n>value</attribute.n>
<group1...n>
<recordid>id</recordid>
<attribute1...n>value</attribute1>
</group>
</record>So:
1/ can anyone think /know of a better way to import xml into access and have the tables linked
2/ what is the way to deal with 100Mb strings optimally?
Current code, which I accept may be no good (although it works!)!:
Code:
'sXML: entire file
'sXMLPart: single record
Loopage:
'isolate this record markers
iStartRecord = InStr(iStart, sXML, sRecordOpener) + Len(sRecordOpener) 'VBA InStr returns 0 if not found
iEndRecord = InStr(iStartRecord, sXML, sRecordClose)
k = iStartRecord - Len(sRecordOpener)
If k = 0 Then: k = 1
sXMLpart = Mid$(sXML, k, iEndRecord - iStartRecord + Len(sRecordOpener) + Len(sRecordClose))
'VBA Instr returns 0 if not found
If iStartRecord > (0 + Len(sRecordOpener)) Then
'find recordid
iStart = InStr(iStartRecord, sXML, sRecordOpener) + Len(sRecordOpener)
iEnd = iStart + 13
sRecordID = Mid$(sXML, iStart, iEnd - iStart)
'Debug.Print sRecordID
For x = 1 To UBound(sTagToAddKeyTo) 'for each tag we want to add a key to
sFind = sTagToAddKeyTo(x) 'the opening tag we're looking for
sFindEnd = sTagToAddKeyToClose(x) 'the closing of that tag
iPointer = iStartRecord 'temp marker
Do While InStr(iPointer, sXMLpart, sFind) < iEndRecord And InStr(iPointer, sXMLpart, sFind) <> 0 'do while instances are found
iStart = InStr(iPointer, sXMLpart, sFind) 'where occurance is found
sXMLpart = Left$(sXMLpart, iPointer - 1) & Replace$(sXMLpart, sFind, sFind & sRecordOpener & sRecordID & sRecordClose, iPointer, 1) 'add key
iPointer = iStart 'move pointer
iPointer = InStr(iPointer, sXMLpart, sFindEnd) + Len(sFindEnd) 'move pointer beyond the key we've added
Loop 'loop per instance of tag
Next x 'next tag within record
iStart = iEndRecord
vff = FreeFile
Open sOutput For Append As #vff
Print #vff, sXMLpart
Close #vff
GoTo Loopage 'next record
