Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 901
  • Last Modified:

How to Parse XML in VBA (MS Access 2000)

I'm using Access 2000. I currently have the text of the XML file that I want to parse now in Access 2000 from the web.  Now I'm trying to determine the 'easiest way' of parsing the data. Here is a shortened version of what the xml text looks like: strFullXML = 'what you see below'
'**************************************************************************************
<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
- <VINquery Version="1.0.0" Report_Type="EXTENDED" Date="3/31/2007">
- <VIN Number="3G1FP32G8Y2128461" Status="SUCCESS">
- <Vehicle VINquery_Vehicle_ID="21550" Model_Year="2000" Make="Chevrolet" Model="Camaro" Trim_Level="Z28 CONVERTIBLE">
  <Item Key="VINquery_Vehicle_ID" Value="21550" Unit="" />
  <Item Key="Model Year" Value="2000" Unit="" />
  <Item Key="Make" Value="Chevrolet" Unit="" />
  <Item Key="Model" Value="Camaro" Unit="" />
  <Item Key="Trim Level" Value="Z28 CONVERTIBLE" Unit="" />
  <Item Key="Manufactured in" Value="CANADA" Unit="" />
  </Vehicle>
  </VIN>
  </VINquery>
'*******************************************************************************
So I've reference Microsoft XML, Version 4 in my application and tried to step through the some basic test code but it bombs.....any ideas as to what I'm doing wrong here ??

'***************************************************************
Dim xmlDoc As New DOMDocument
Dim currNode As IXMLDOMNode
Dim rootNode As IXMLDOMNode
Dim oNodeList As IXMLDOMNodeList
Dim strFullXML as string

strFullXML = 'the text that is shown above'

xmlDoc.async = False
xmlDoc.load(strFullXML)
Set rootNode = xmlDoc.documentElement
'
' Display the XML for the root node's first child.
'
Set currNode = xmlDoc.documentElement.firstChild
MsgBox currNode.xml
'
' Get a list of the root's children and display the XML for each child.
'
Set oNodeList = rootNode.childNodes
For Each currNode in oNodeList
  MsgBox currNode.xml
Next
'
' Get a node, get its left sibling, display its XML.
'
Set currNode = xmlDoc.documentElement.childNodes.item(1)
Set newNode = currNode.previousSibling
MsgBox newNode.xml
0
DonGarry
Asked:
DonGarry
  • 2
  • 2
1 Solution
 
MikeTooleCommented:
The Load method is used to open an XML file, you need LoadXML to load from a string.
It's worthwhile testing the result of the Load, whichever way you do it:

If not xmlDoc.LoadXML(strFullXML) then
    ' Error handling
    ...
end if

Also, newNode isn't declared - I always have Option Explicit on, saves a whole load of hassle.

I saved your xml to a file and executed your code using the Load method to load the file, no problem.

Hope this helps
Mike
0
 
DonGarryAuthor Commented:
Thanks Mike for your speedy response....
I'm still having difficulty getting this to work and it fails when I use your suggestion of testing the load.

Maybe I should back up and tell you how I'm getting the XML string  which is through the active X control Web Browser.
'************************************
Dim xmlDoc As New DOMDocument
Dim currNode As IXMLDOMNode
Dim newNode As IXMLDOMNode
Dim rootNode As IXMLDOMNode
Dim oNodeList As IXMLDOMNodeList
Dim strFullXML as string

Me.ocxWebBrowser.Navigate strURL 'strURL is created by my application
strFullXML = Me.ocxWebBrowser.Document.documentElement.innerText

'Now I'm trying your suggested test here
If not xmlDoc.LoadXML(strFullXML) then
      'It bombs right here...hmm
End If
'******************************************************************
Any further suggestions ?
0
 
MikeTooleCommented:
Mmm, it loaded OK from a file, but not from the string. This code at least will give you the error message - I'm not sure of the cause - and I may have introduced an error just by constructing your string in the function. Try it  out using the output from your control:

Option Compare Database
Option Explicit

Function xmlstring() As String
xmlstring = "<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes"" ?>" _
& "- <VINquery Version=""1.0.0"" Report_Type=""EXTENDED"" Date=""3/31/2007"">" _
& "- <VIN Number=""3G1FP32G8Y2128461"" Status=""SUCCESS"">" _
& "- <Vehicle VINquery_Vehicle_ID=""21550"" Model_Year=""2000"" Make=""Chevrolet"" Model=""Camaro"" Trim_Level=""Z28 CONVERTIBLE"">" _
& "<Item Key=""VINquery_Vehicle_ID"" Value=""21550"" Unit="""" />" _
& "  <Item Key=""Model Year"" Value=""2000"" Unit="""" />" _
& "  <Item Key=""Make"" Value=""Chevrolet"" Unit="""" />" _
& "  <Item Key=""Model"" Value=""Camaro"" Unit="""" />" _
& "  <Item Key=""Trim Level"" Value=""Z28 CONVERTIBLE"" Unit="""" />" _
& "  <Item Key=""Manufactured in"" Value=""CANADA"" Unit="""" />" _
& "  </Vehicle>" _
& "  </VIN>" _
& "  </VINquery>"

End Function

Sub TestIt()
Dim xmlDoc As New DOMDocument
Dim currNode As IXMLDOMNode
Dim rootNode As IXMLDOMNode
Dim oNodeList As IXMLDOMNodeList
Dim NewNode As IXMLDOMNode
Dim strFullXML As String


'strFullXML = 'the text that is shown above'

xmlDoc.async = False
'If Not xmlDoc.Load("C:\Documents and Settings\MikeT\My Documents\Technical\ExpertsExchange\xmltest.xml") Then

With xmlDoc
    If Not xmlDoc.loadXML(xmlstring()) Then
        MsgBox "Line: " & .parseError.Line & vbCrLf & _
                "Char: " & .parseError.linepos & vbCrLf & _
                "Text: ...'" & Mid(.parseError.srcText, 57, 20) & "...'" & vbCrLf & _
                "Reason: " & .parseError.reason
        Exit Sub
    End If
End With
'xmlDoc.loadXML

Set rootNode = xmlDoc.documentElement
'
' Display the XML for the root node's first child.
'
Set currNode = xmlDoc.documentElement.firstChild
MsgBox currNode.XML
'
' Get a list of the root's children and display the XML for each child.
'
Set oNodeList = rootNode.childNodes
For Each currNode In oNodeList
  MsgBox currNode.XML
Next
'
' Get a node, get its left sibling, display its XML.
'
Set currNode = xmlDoc.documentElement.childNodes.Item(1)
Set NewNode = currNode.previousSibling
MsgBox NewNode.XML

'Authored by: DonGarry

End Sub
0
 
DonGarryAuthor Commented:
Hi Mike, thanks again for your help, what your code told me was that there was a few dashes in the xml string that shouldn't really be there and was causing the error. I'll go back to the people who supply the xml and ask some questions.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now