cyraxx
04-25-2013, 06:58 AM
Hello everyone :hi:
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 :banghead:
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 :cloud9:
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
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 :banghead:
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 :cloud9:
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