XML Transform Requirement to Import into Access

[JohanGduToit]

Greetings Experts,

I am trying to import multiple XML files into Access making use of the Application.ImportXML method; but am unable to link the imported 'Parent' / 'Child' tables due to the fact there are no 'common' fields that I can use to create a relationship between these imported tables.

Research suggests that I need to make use of an .XSL file that will transform the original XML file to enable such linking. I have found the code below that will handle the importing; but specifically require help with creating the "tranformer.xsl" file that is mentioned in the code:

Public Sub TransformAndImportMultipleXMLs()

Dim strFile As String, strPath As String
' REFERENCE MS XML, v6.0
Dim xmlDoc As New MSXML2.DOMDocument60, xslDoc As New MSXML2.DOMDocument60
Dim newDoc As New MSXML2.DOMDocument60

strPath = "C:\test\"
strFile = Dir(strPath & "*.xml")

' LOAD XSL ONLY ONCE
xslDoc.Load "C:\test\transformer.xsl"

While strFile <> ""
' REINITIALIZE DOM OBJECTS
Set xmlDoc = New MSXML2.DOMDocument60
Set newDoc = New MSXML2.DOMDocument60

' LOAD XML SOURCE
xmlDoc.Load strPath & strFile

' TRANSFORM SOURCE
xmlDoc.transformNodeToObject xslDoc, newDoc
newDoc.Save "C:\test\temp.xml"

' APPEND TO TABLES
Application.ImportXML "C:\test\temp.xml", acAppendData
strFile = Dir()
Wend

' RELEASE DOM OBJECTS
Set xmlDoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing

End Sub

I have attached an image in an attempt to explain what the transformed XML file should look like for me to establish a relationship between the "AgentInvoice" and "AgentInvoiceLine" tables, as well as between the "CustomsDeclarationLine" and "LandedCostDetails" tables. Please note that I have edited the original XML file by deleting quite a few "irrelevant" elements and attributes not related to my requirement.

I would REALLY appreciate it if somebody can provide me with an .XSL file that would handle the required transformation. I'm completely in the dark here!


Many Thanks.