更新时间:2023-01-12 19:35:38
这是一个可行的解决方案.下面的方法需要在访问表单中,该表单将显示XML数据.表单中的文本字段应设置为其"Contol源"具有与ADODB记录集中添加的字段相同的名称.
Here is a working solution. The method below needs to be in the Access Form which will display the XML data. The text fields in the form should be set that their 'Contol source' has the same names as the fields addedd in the ADODB recordset.
Private Sub GetXMLdata()
On Error GoTo ErrorHandler
'************************************************************
'CREATE AN ADODB RECORDSET - this recordset is in memory only it does not create a table in the database file
'This requires a reference addedd in TOOLS > References, Microsfot ActiveX Data Object , the latest version...
'************************************************************
Dim rs As ADODB.Recordset
Dim fld As ADODB.field
Dim strXML As String
Set rs = New ADODB.Recordset
With rs
.Fields.Append "EventID", adVarChar, 15, adFldMayBeNull
.Fields.Append "JobDescription", adVarChar, 255, adFldMayBeNull
.Fields.Append "FullName", adVarChar, 100, adFldMayBeNull
.Fields.Append "CustomerID", adVarChar, 15, adFldMayBeNull
.Fields.Append "CustomerAddress", adVarChar, 255, adFldMayBeNull
.Fields.Append "Town", adVarChar, 64, adFldMayBeNull
.Fields.Append "PostCode", adVarChar, 20, adFldMayBeNull
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockPessimistic
.Open
End With
'**********************************************************
'DOWNLOAD XML DATA
'**********************************************************
Dim obj As MSXML2.ServerXMLHTTP
Set obj = New MSXML2.ServerXMLHTTP
bj.Open "GET", "http://www.myserver.com/mydata.xml", False
'in case you are sending a form *POST* or XML data to a SOAP server set content type
obj.setRequestHeader "Content-Type", "text/xml"
obj.send
Dim status As Integer
status = obj.status
If status >= 400 And status <= 599 Then
Debug.Print "Error Occurred : " & obj.status & " - " & obj.statusText
End If
'**********************************************************
'CREATE XML DOM DOCUMENT
'**********************************************************
Dim xmlDoc As MSXML2.DOMDocument
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlNode As MSXML2.IXMLDOMElement
Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.loadXML (obj.responseText)
'**********************************************************
'LOAD XML DATA INTO THE RECORDSET
'**********************************************************
LoadNodesIntoRs xmlDoc.childNodes, rs, 0
If rs.recordCount > 0 Then
rs.Update
'BOUND THIS RECORDSET TO THE FORM
Set Me.Recordset = rs
End If
Exit Sub
ErrorHandler:
MsgBox Err.Description
End Sub
下面的方法在传递的记录集中一一输入字段.因为MSXML2似乎跳过了<something></something>
之类的空标签,所以每个带有数据的标签名称都需要按名称进行检查,并输入到适当的记录集字段中.
The method below enters one by one field into the passed recordset. Because MSXML2 seems to skip empty tags like <something></something>
each tag name with data needs to be checked by name and entered into an appropriate recordset field.
Public Sub LoadNodesIntoRs(ByRef nodes As MSXML2.IXMLDOMNodeList, rs As ADODB.Recordset, recordCount As Integer)
Dim xNode As MSXML2.IXMLDOMNode
Dim fieldIndex As Integer
For Each xNode In nodes
If xNode.nodeType = NODE_TEXT Then
'a field - actual data
'note that MSXML2 will skip any node which contain no data like <COMPANY></COMPANY>
Select Case xNode.parentNode.nodeName
Case "EVENTID"
fieldIndex = 0
Case "DESCRIPTION"
fieldIndex = 1
Case "NAME"
fieldIndex = 2
Case "CUSTOMERID"
fieldIndex = 3
Case "ADDRESS"
fieldIndex = 4
Case "TOWN"
fieldIndex = 5
Case "POSTALCODE"
fieldIndex = 6
End Select
rs(fieldIndex) = xNode.nodeValue
Else
'CHECK FOR THE NODE WHICH CONTAINS THE SETS OF DATA'
If xNode.parentNode.nodeName = "data" Then
'next record
If recordCount > 0 Then
'save previous record
rs.Update
fieldIndex = 0
End If
rs.AddNew
recordCount = recordCount + 1
End If
End If
If xNode.hasChildNodes Then
'recurive call for the next node
LoadNodesIntoRs xNode.childNodes, rs, recordCount
End If
Next xNode
End Sub