Import Specific XML elements into variable using VBA

[Meichmann]

Hello Everyone,
I have a troubling issue and I hope you can help me. I have an xml document which stores information about an address:

<ResourceSets>
-<ResourceSet>
<EstimatedTotal>1</EstimatedTotal>
-<Resources>
-<Location>
<Name>Address Here</Name>
-<Point>
<Latitude>Numerical Latitude</Latitude>
<Longitude>Numerical Longitude</Longitude>
</Point>


<Location>
</Resources>
</ResourceSet>
</ResourceSets>

The acutal xml file was edit to only show what i need. What i am looking to do is extract the "Latitude" and "Longitude" text values and place them in a variable using VBA. I am just unsure of the drilldown into the child nodes and sub child nodes to get it.

Thank you!

[June7]

Did you try import to a table?

You can open the xml file as a text file object in VBA. Read each line and test for the words Latitude and Longitude in the string and parse the numeric values.

[Meichmann]

Did you try import to a table?

You can open the xml file as a text file object in VBA. Read each line and test for the words Latitude and Longitude in the string and parse the numeric values.

yes i did. everytime i go to import the xml file it creates new tables, it doesnt overwrite the data in the previous tables. this xml file will be constantly rewritten because of queries.

[June7]

Those are the only options I am aware of.

You want the previous table overwritten? Code can delete the table then the new import take its place.

[ItsMe]

To add to June's comments...

You can save the import procedure and then call the procedure using VBA.
DoCmd.RunSavedImportExport "SavedProcedureName"

You can use the following to delete a table within the current DB
DoCmd.DeleteObject acTable, "TableName"

If you want to append a table with an XML file, you can import the XML file and then import the same file a second time. On the second import, select the option to append existing tables and then save THAT procedure with a meaningful name. Since you imported the same file twice, you will need to delete some records in your permanent table.

[Meichmann]

You can load the xml file into memory using the "xmlDoc.Load (Name)" command, so saving the data to a table is not necessary. I just need the code to get to the "Latitude" and "Longitude" nodes and extract the values.

I have this code:

Set NodeList = xmlDoc.getElementsByTagName("Response")

For Each iNode In NodeList
For Each iAtt In iNode.Attributes
MsgBox iAtt.Name & ": " & iAtt.nodeTypedValue
Next

This code returns all the attributes in the "response" node, which is the first node in the file right below the declaration. The response node was omitted because it just holds metadata.
What i need is to drill down to the "Point" node and extract the values of the siblings.

Importing data into a table is not an option because of permission settings in the application.

[ItsMe]

You will have to nest your loops as you enumerate each parent. Maybe something like this. You could possibly go directly to the child node with the following code but, maybe the parent nodes have info you need. So tthis should iterate through Response and Point. Test it out and see....

Dim xmlDoc As New DOMDocument60
Dim selNodeList As IXMLDOMSelection
Dim iNode As IXMLDOMNode
Dim selNodePoint As IXMLDOMSelection
Dim iPoint As IXMLDOMNode
Dim elmLat As IXMLDOMElement
Dim elmLon As IXMLDOMElement
Dim intResponse As Integer
intResponse = 0
xmlDoc.Load "C:\Test\Sample.xml"
    Set selNodeList = xmlDoc.getElementsByTagName("Response")
    
    For Each iNode In selNodeList
    Debug.Print "Response Node Index = " & intResponse
    
        Set selNodePoint = xmlDoc.getElementsByTagName("Point")
        
        For Each iPoint In selNodePoint
            
            Set elmLat = iPoint.childNodes(0)
            Set elmLon = iPoint.childNodes(1)
        Debug.Print "Point Node Value = " & elmLat.nodeTypedValue & "Lat " & elmLon.nodeTypedValue & " Lon"
        Next    'iPont
    
    intResponse = intResponse + 1
    Next    'iNode

[Meichmann]

I was able to figure it out. here is the code:


Dim xmlDoc As New DOMDocument
Dim iNode As MSXML2.IXMLDOMNode
Dim iNode2 As MSXML2.IXMLDOMNode
Dim NodeList As IXMLDOMNodeList
Dim strAddress As String
Dim strCoordinates As String
strAddress = UrlAddress

xmlDoc.async = False
xmlDoc.Load strAddress
xmlDoc.Save ("c:\Access\Geocode.xml")
xmlDoc.Load ("c:\Access\Geocode.xml")

'If xmlDoc.parseError.ErrorCode <> 0 Then
' MsgBox "Invalid XML, Load Failed"
' Else
' MsgBox "Xml Loaded Successfully"
'End If

Set NodeList = xmlDoc.SelectNodes("//Response/ResourceSets/ResourceSet/Resources/Location/Point")
For Each iNode In NodeList
For Each iNode2 In iNode.ChildNodes
If iNode2.nodeName = "Latitude" Then
strCoordinates = iNode2.nodeTypedValue
End If

If iNode2.nodeName = "Longitude" Then
strCoordinates = strCoordinates & "," & iNode2.nodeTypedValue
End If

Next iNode2
Next iNode

I recieved the message that you replied ItsMe after i figured it out. but thanks for replying!