Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to Parse XML in VBA (MS Access 2000)

Posted on 2007-03-31
4
Medium Priority
?
870 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

618 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