-
Reading XML issues
I'm pulling in some XML to be read by VBA, but instead of the XML just a having a simple tag called <product> it actually has this format...
Code:
<Product xmlns="xttp://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="xttp://mws.amazonservices.com/sc[/FONT]hema/Products/2011-10-01/default.xsd">
(I've had to put a x at the front of the URL above as I'm new & can't post links!)
My question is how are you supposed to handle such a long XML tag?
Here's the code I'm using (which works if I strip back the long tag above to be just <product>)...
Code:
Public Function READXML() 'Reference Microsoft XML 6.0 required
Dim testDOM As DOMDocument60
Dim objRoot As IXMLDOMElement
Dim strMsg As String
Set testDOM = New DOMDocument60
testDOM.resolveExternals = False
testDOM.validateOnParse = False
testDOM.Load ("C:\test.xml")
Set objRoot = testDOM.documentElement
txtPrice = objRoot.selectSingleNode("/Product/CompetitivePricing/CompetitivePrices/CompetitivePrice/Price/LandedPrice/Amount").text
Debug.Print txtPrice
End Function
& here's the XML in full
Code:
<Product xmlns="Xttp://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="Xttp://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd">
<Identifiers>
<MarketplaceASIN>
<MarketplaceId>A1F83G8C2ARO7Q</MarketplaceId>
<ASIN>B005V38BZ8</ASIN>
</MarketplaceASIN>
</Identifiers>
<CompetitivePricing>
<CompetitivePrices>
<CompetitivePrice belongsToRequester="true" condition="New" subcondition="New">
<CompetitivePriceId>1</CompetitivePriceId>
<Price>
<LandedPrice>
<CurrencyCode>GBP</CurrencyCode>
<Amount>8.61</Amount>
</LandedPrice>
<ListingPrice>
<CurrencyCode>GBP</CurrencyCode>
<Amount>8.61</Amount>
</ListingPrice>
<Shipping>
<CurrencyCode>GBP</CurrencyCode>
<Amount>0.00</Amount>
</Shipping>
</Price>
</CompetitivePrice>
</CompetitivePrices>
<NumberOfOfferListings>
<OfferListingCount condition="New">10</OfferListingCount>
<OfferListingCount condition="Any">10</OfferListingCount>
</NumberOfOfferListings>
</CompetitivePricing>
<SalesRankings>
<SalesRank>
<ProductCategoryId>ce_display_on_website</ProductCategoryId>
<Rank>25388</Rank>
</SalesRank>
<SalesRank>
<ProductCategoryId>1939024031</ProductCategoryId>
<Rank>22</Rank>
</SalesRank>
</SalesRankings>
</Product>
