You did not post what the structure looks like, post some xml files so I can give you some use cases.
For now, I can only advice querying the xml files like you would query an html file, requires a reference to Microsoft XML 6.0 but can be converted to a late bound version by simply changing the msxml references to objects
I had these xml files at hand that have similar structures. All these files shared columns "invoice:Sender" and "invoice:Receiver" and had an attribute "Name", so this is what I used to query them all.
Code:
Sub LoopOverTags()
Dim fileName As String
Dim filePath As String
Dim tagNames As Variant
Dim i As Long
Dim attributeValue As String
' Set the directory path
filePath = CurrentProject.Path
' Set the initial file name
fileName = Dir(filePath & "\*.xml")
' Set the array of tag names
tagNames = Array("invoice:Sender", "invoice:Receiver")
' Loop through all the XML files in the directory
Do While fileName <> ""
' Loop through all the tag names
For i = LBound(tagNames) To UBound(tagNames)
' Get the attribute value
attributeValue = GetAttributeValue(filePath & "" & fileName, CStr(tagNames(i)), "Name")
' Do something with the attribute value
Debug.Print attributeValue
Next i
' Get the next XML file name
fileName = Dir
Loop
End Sub
Code:
Private Function GetAttributeValue(filePath As String, tagName As String, attributeName As String) As String
Dim xmlDoc As New MSXML2.DOMDocument60
Dim tagNodeList As MSXML2.IXMLDOMNodeList
Dim tagNode As MSXML2.IXMLDOMNode
Dim attributeValue As String
' Load the XML document
xmlDoc.Load filePath
' Select the element with the specified tag name
Set tagNodeList = xmlDoc.getElementsByTagName(tagName)
Set tagNode = tagNodeList.Item(0)
' Retrieve the value of the attribute with the specified name
attributeValue = tagNode.Attributes.getNamedItem(attributeName).Text
' Return the value
GetAttributeValue = attributeValue
End Function
The rest of the code necessary for your use case is basically database code to form an insert/update statement inside the loop.
The code above expects you to have a list of xml files in the current project path, which can be changed. To use it, open your xml, check its structure, find the tags you're interested in and their attribute and input in the function. As I mentioned, in order to send the data to tables, you need to form the statements, the rest of the work is done.