Solved

How to Parse XML in VBA (MS Access 2000)

Posted on 2007-03-31
4
783 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
[X]
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
  • 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

726 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