'SelectSingleNode'XPath查询从XML与多个命名空间 [英] 'SelectSingleNode' XPath query from XML with multiple Namespaces
问题描述
这是我试图解析的示例XML的一部分:
< GetCompetitivePricingForASINResponse xmlns =http:// mws .amazonservices.com /模式/产品/ 2011-10-01\" >
< GetCompetitivePricingForASINResult ASIN =B014P3CM08status =Success>
< Product xmlns =http://mws.amazonservices.com/schema/Products/2011-10-01xmlns:ns2 =http://mws.amazonservices.com/schema/Products/2011 -10-01 / default.xsd>
<标识符>
< MarketplaceASIN>
< MarketplaceId> A1F83G8C2ARO7P< / MarketplaceId>
< ASIN> B014P3CM08< / ASIN>
< / MarketplaceASIN>
< /标识符>
<竞争性定价>
< CompetitivePrices>
< CompetitivePrice belongsToRequester =falsecondition =Newsubondition =New>
< CompetitivePriceId> 1< / CompetitivePriceId>
< Price>
< LandedPrice>
< CurrencyCode> GBP< / CurrencyCode>
< Amount> 24.00< / Amount>
< / LandedPrice>
< ListingPrice>
< CurrencyCode> GBP< / CurrencyCode>
< Amount> 24.00< / Amount>
< / ListingPrice>
< Shipping>
< CurrencyCode> GBP< / CurrencyCode>
< Amount> 0.00< / Amount>
< / Shipping>
< / Price>
< / CompetitivePrice>
< / CompetitivePrices>
.... etc
我正在尝试从/价格/ LandedPrice / Amount / node( 24.00
)。以下是迄今为止的VBA代码:
Dim strXMLSite As String
pre>
Dim objXMLHTTP As MSXML2.XMLHTTP60
Dim objXMLDoc As MSXML2.DOMDocument60
Dim objXMLNode1 As MSXML2.IXMLDOMNode
设置objXMLHTTP =新建MSXML2.XMLHTTP60
设置objXMLDoc =新建MSXML2.DOMDocument60
调用MWSProductAPI
strXMLSite =工作表(设置)。范围(B12)。值
objXMLHTTP.OpenGET,strXMLSite,False
objXMLHTTP.send
objXMLDoc.LoadXML(objXMLHTTP.responseText)
XmlNamespaces =xmlns:ns2 ='http://mws.amazonservices.com/schema/Products/2011-10-01 /default.xsd'xmlns:ns1 ='http://mws.amazonservices.com/schema/Products/2011-10-01'
objXMLDoc.setPropertySelectionNamespaces,XmlNamespaces
设置objXMLNode1 = objXMLDoc.SelectSingleNode(/ ns2:GetCompetitivePricingForASINResponse / ns2:GetCompetitivePricingForASINResult / ns2:Product / ns2:CompetitivePricing / ns2:CompetitivePrices / ns2:CompetitivePrice / ns2:Price / ns2:LandedPrice / ns2:Amount)
工作表(Settings)。Range(C8)。Value = objXMLNode1.text
但是,当我在Excel中运行代码时,会返回错误:
'对象变量或块引用未设置'。
调试代码显示SelectSingleNode没有返回任何内容。我对命名空间前缀的理解以及它如何适用于XPath非常有限。我可以找到很多有命名空间的XML的例子,但是有多个命名空间的例子非常有限。
解决方案因为你在根标记中有一个默认的命名空间,你只需要声明一个命名空间,而不是多个。考虑以下调整来删除其他声明:
XmlNamespaces =xmlns:ns2 ='http://mws.amazonservices。 com / schema / Products / 2011-10-01'
objXMLDoc.setPropertySelectionNamespaces,XmlNamespaces
设置objXMLNode1 = objXMLDoc.SelectSingleNode(/ ns2:GetCompetitivePricingForASINResponse _
&/ ns2:GetCompetitivePricingForASINResult / ns2:Product_
&/ ns2:CompetitivePricing / ns2:CompetitivePrices_
&/ ns2:CompetitivePrice / ns2: / ns2:LandedPrice / ns2:Amount)
顺便说一句,您可以缩短XPath表达式: / p>
设置objXMLNode1 = objXMLDoc.SelectSingleNode(// ns2:Price / ns2:LandedPrice / ns2:Amount)
即使使用列表,应该出现多个
LandedPrice
将声明更改为节点列表,并使用索引notat离子返回值):...
Dim objXMLNode1 As MSXML2.IXMLDOMNodeList
...
设置objXMLNode1 = objXMLDoc.DocumentElement.SelectNodes(_
& // ns2:Price / ns2:LandedPrice / ns2:Amount)
工作表(Settings)。Range(C8)。Value = objXMLNode1(0).Text
Here's part of an example XML I am trying to parse:
<GetCompetitivePricingForASINResponse xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01"> <GetCompetitivePricingForASINResult ASIN="B014P3CM08" status="Success"> <Product xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd"> <Identifiers> <MarketplaceASIN> <MarketplaceId>A1F83G8C2ARO7P</MarketplaceId> <ASIN>B014P3CM08</ASIN> </MarketplaceASIN> </Identifiers> <CompetitivePricing> <CompetitivePrices> <CompetitivePrice belongsToRequester="false" condition="New" subcondition="New"> <CompetitivePriceId>1</CompetitivePriceId> <Price> <LandedPrice> <CurrencyCode>GBP</CurrencyCode> <Amount>24.00</Amount> </LandedPrice> <ListingPrice> <CurrencyCode>GBP</CurrencyCode> <Amount>24.00</Amount> </ListingPrice> <Shipping> <CurrencyCode>GBP</CurrencyCode> <Amount>0.00</Amount> </Shipping> </Price> </CompetitivePrice> </CompetitivePrices> .... etc
I am trying to target and retrieve text from the /Price/LandedPrice/Amount/ node (
24.00
in this case). Here's the VBA code I have so far:Dim strXMLSite As String Dim objXMLHTTP As MSXML2.XMLHTTP60 Dim objXMLDoc As MSXML2.DOMDocument60 Dim objXMLNode1 As MSXML2.IXMLDOMNode Set objXMLHTTP = New MSXML2.XMLHTTP60 Set objXMLDoc = New MSXML2.DOMDocument60 Call MWSProductAPI strXMLSite = Worksheets("Settings").Range("B12").Value objXMLHTTP.Open "GET", strXMLSite, False objXMLHTTP.send objXMLDoc.LoadXML (objXMLHTTP.responseText) XmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd' xmlns:ns1='http://mws.amazonservices.com/schema/Products/2011-10-01'" objXMLDoc.setProperty "SelectionNamespaces", XmlNamespaces Set objXMLNode1 = objXMLDoc.SelectSingleNode("/ns2:GetCompetitivePricingForASINResponse/ns2:GetCompetitivePricingForASINResult/ns2:Product/ns2:CompetitivePricing/ns2:CompetitivePrices/ns2:CompetitivePrice/ns2:Price/ns2:LandedPrice/ns2:Amount") Worksheets("Settings").Range("C8").Value = objXMLNode1.text
However, when I run the code in Excel it returns the error:
'Object variable or With block reference not set'.
Debugging the code shows that the SelectSingleNode is returning nothing. My understanding of the namespace prefix and how this fits into the XPath is very limited. I can find plenty of examples for XML with namespaces, however, examples where there are multiple namespaces are very limited.
解决方案Because you have a default namespace in root tag, you only need to declare one namespace, not multiple. Consider the following adjustment in removing the other declaration:
XmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01'" objXMLDoc.setProperty "SelectionNamespaces", XmlNamespaces Set objXMLNode1 = objXMLDoc.SelectSingleNode("/ns2:GetCompetitivePricingForASINResponse" _ & "/ns2:GetCompetitivePricingForASINResult/ns2:Product" _ & "/ns2:CompetitivePricing/ns2:CompetitivePrices" _ & "/ns2:CompetitivePrice/ns2:Price/ns2:LandedPrice/ns2:Amount")
By the way you can shorten the XPath expression:
Set objXMLNode1 = objXMLDoc.SelectSingleNode("//ns2:Price/ns2:LandedPrice/ns2:Amount")
Even use a list should more than one
LandedPrice
appear (but change declaration as node list and use index notation for return value):... Dim objXMLNode1 As MSXML2.IXMLDOMNodeList ... Set objXMLNode1 = objXMLDoc.DocumentElement.SelectNodes(" _ & //ns2:Price/ns2:LandedPrice/ns2:Amount") Worksheets("Settings").Range("C8").Value = objXMLNode1(0).Text
这篇关于'SelectSingleNode'XPath查询从XML与多个命名空间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!