Solved

How to Parse XML in VBA (MS Access 2000)

Posted on 2007-03-31
4
705 Views
Last Modified: 2013-11-27
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
Comment
Question by:DonGarry
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:MikeToole
Comment Utility
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
 

Author Comment

by:DonGarry
Comment Utility
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
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
Comment Utility
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
 

Author Comment

by:DonGarry
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now