Excel 中的 XML 命名空间

Question

我正在尝试使用 Excel 中的 OpenStreetMap 旅行时间构建距离矩阵。我使用 YOURS API 和 Google Maps API excel 模块作为引用( http://oco-carbon.com/2012/05/17/a-google-maps-journey-time-function-for-excel/ )。

这是我试图引入 Excel 的 XML 示例:http://www.yournavigation.org/api/1.0/gosmore.php?format=xml&flat=60.480398&flon=22.277206&tlat=60.402923&tlon=22.355558&v=motorcar&fast=1&layer=mapnik

目前我的 VB 代码如下所示:

Function G_TIME(Flat As String, Flon As String, Tlat As String, Tlon As String) As Double
' Requires a reference to Microsoft XML, v6.0
' Draws on the stackoverflow answer at bit.ly/parseXML
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim timeNode As IXMLDOMNode

G_TIME = 0
On Error GoTo exitRoute
' Check and clean inputs
' Origin = Replace(Origin, " ", "%20")
' Destination = Replace(Destination, " ", "%20")
' Read the XML data from the Google Maps API
Set myRequest = New XMLHTTP60
' myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
'    & Origin & "&destination=" & Destination & "&sensor=false", False
myRequest.Open "GET", "http://www.yournavigation.org/api/1.0/gosmore.php?format=kml&flat=" & Flat & "&flon=" & Flon & "&tlat=" & Tlat & "&tlon=" & Tlon & "&v=motorcar&fast=1&layer=mapnik", False
myRequest.Send
' Make the XML readable usign XPath
Set myDomDoc = New DOMDocument60
myDomDoc.LoadXML myRequest.responseText
' Get the time node value
Set timeNode = myDomDoc.SelectSingleNode("//Document/traveltime")
'If Format = "Decimal" Then ' Return as a decimal - 30 mins as 0.5 hrs
'    G_TIME = timeNode.Text ' Seconds in an hour
'Else 'Return in Excel's 00:00:00 date format - 30 mins as 00:30:00
    G_TIME = timeNode.Text ' Seconds in a day
'End If
exitRoute:
' Tidy up
Set timeNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
End Function

我认为问题出在这里:

Set timeNode = myDomDoc.SelectSingleNode("//Document/traveltime")

YOURS XML 使用 Google Earth KML 样式，需要为 Excel 定义。我试过使用 XmlNamespaceManager 但我无法让它工作。我想我需要导入一些东西，但我不熟悉 VB for Excel，所以我不知道在哪里做。

任何帮助表示赞赏!

Answer 1

在 Microsoft 的 DOMDocuments 中使用 XPath 查询时，默认命名空间存在问题。您需要给默认命名空间一个前缀，然后在 XPath 查询中使用该前缀 - 像这样:

Set myDomDoc = New DOMDocument60
myDomDoc.loadXML myRequest.responseText
myDomDoc.setProperty "SelectionNamespaces", "xmlns:r='http://earth.google.com/kml/2.0'"

' Get the time node value
Set timeNode = myDomDoc.selectSingleNode("//r:Document/r:traveltime")

更多信息请访问 here