Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to Parse XML in VBA (MS Access 2000)

Posted on 2007-03-31
4
766 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
ID: 18831594
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
ID: 18832240
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
ID: 18832958
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
ID: 18834672
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 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