XLS File required to Transform XML File

[JohanDT]

Greetings All Access Experts,

I have been battling to import multiple XML files into Access making use of the Application.ImportXML method. I am not able to create relationships between imported tables since there are no "mutual" or "relatable" fields being imported which I can use to establish a relationship between them.

Search results indicate that I would require an XSL file (as per the module below) to transform the original XML files to accomplish this.

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 a copy of the original XML file (LandedCost.zip) as well as an image in an attempt to explain what I would like the XSL file to achieve.

In essence, the "DocumentNumber" attribute & value from the "AgentInvoice" section should be copied (included) in the "AgentInvoiceLine" section below it and the "LineNumber" attribute & value from the "CustomsDeclarationLine" section should be copied (included) in the "LandedCostDetails" section. (Apologies for referring to 'sections'; I'm not familiar with all of the XML naming conventions).

By adding the "DocumentNumber" attribute & value to the "AgentInvoiceLine" element would allow me to link imported tables "AgentInvoice" and "AgentInvoiceLine" on the "DocumentNumber" field. Likewise, by adding the "LineNumber" attribute & value to the "LandedCostDetails" element, the "CustomsDeclarationLine" and "LandedCostDetails" tables could be linked on the "LineNumber" field.

Please reach out if you have any questions, I'm not always that clear when describing a problem

I sincerely hope that this can be achieved via an XSL file and a solution to this would really go a very long way in our quest to automate the processing of XML files into our Access' Shipping Management System.

I thank you in advance.