VBA Access: Parsing XML Contents to Table

[cyraxx]

Hello everyone

I'm having some major head banging on this project.


First and foremost: This Access table is different. It is different in that USUALLY values or data, are made in rows. In this case, each row lists a tag or element/attribute, and what is to be put in the attribute/element value is in a column named Values. Please see my example image below:
h*tp://i.imgur.com/a7yBuNX.jpg

Now what I need to do is to somehow automate the reading of an XML to take in the tags, this includes the elements and the attributes for the elements. The thing is, there are WAY more elements/attributes on the table than the ones that are in the XML, so it's possible to have all, some, or none. So what I need to do have it pull the elements that are inputted - if it has a value attached to it, put the value in the "Values" column. After detecting the element we need to detect attributes for said element, and apply any values for them onto the Values column for that respective attribute/element as well.

I have been told to use the GetelementByTag feature, however that is really making me scratch my head. How I am currently trying to do it is simply by pulling all the nodes, and then looping through the recordset of Attributes/Elements (which is over 400 in total!) to see if it matches. I REALLY think this is the wrong way to go about it altogether

Given my inexperience, can someone either show a different way, or tell me what I am doing wrong?

The current bug I am at with my code is that when RecordSet("Element/Attribute") = objNode.nodeName, it tries to change the RecordSet.Values to = objNode.nodeValue, however nodeValue is null so it stops the program? Which I guess is possible, XML tags that are there with no value but just have the tags is often. Many times, the element is present with no value because they are the parent child for an attribute that DOES have a value.

Below is my Code, any help is mucho appreciated

Public Sub Command0_Click()
Dim rs As DAO.Recordset
Dim strXML As String
Dim strSQL As String
Dim Resp As New DOMDocument
Dim objNodeList As IXMLDOMNodeList
Dim objAttribute As IXMLDOMAttribute
Dim objChildNodes As IXMLDOMNodeList
Dim rsAttributes As MSXML2.IXMLDOMNamedNodeMap
Dim xmlDoc As MSXML2.DOMDocument
Dim x As Integer
Dim db As DAO.Database
 
Set db = CurrentDb()
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
'db.Execute ("ALTER TABLE InvoiceResponse ADD COLUMN Values String")
 
    strSQL = "SELECT [ID], [Level], [Element/Attribute], [Occurs], [Data Type], Description FROM InvoiceResponse"
 
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
    Dim obj As MSXML2.DOMDocument
    Set obj = New MSXML2.DOMDocument
    obj.Load ("C:\users\username\documents\vertex\Transaction Tester\test.xml")
    Dim xmlElement As MSXML2.IXMLDOMElement
    Dim xmlNode As MSXML2.IXMLDOMElement
 
    Nodebuilder obj.childNodes, rs
 
    End Sub

Sub Nodebuilder(ByRef objNodeList As IXMLDOMNodeList, ByRef rs As DAO.Recordset)
 
    Dim objNode As IXMLDOMNode
 
        For Each objNode In objNodeList
 
 
        Do Until rs.EOF
        If rs("Element/Attribute").Value = objNode.nodeName Then
            rs.Edit
            rs("Values").Value = objNode.nodeValue
            rs.Update
            Else
            rs.MoveNext
 
            End If
 
        Loop
 
     If objNode.hasChildNodes Then
        Nodebuilder objNode.childNodes, rs
     End If
 
     Next objNode
 
    End Sub