Calling XML experts...

[peskywinnets]

Hiya,

I've got the most simple of XML reading in VBA going on, here's the 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 (for simplicity I'm copying the XML into a file called test.xml, but in reality it comes from an http request)...

<Product>
<Identifiers>
<MarketplaceASIN>
<MarketplaceId>A1F83G8C2ARO8P</MarketplaceId>
<ASIN>B005J38BX5</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>

It all works as I want (i.e. the text between the <amount> </amount> tags is extracted as 8.61 correctly, however, I've actually had to modify the original XML, because the <product> line is actually pulled back from the originating source as this...

<Product xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd">

with that long line in the <product> opening tag, my code doesn't extract the required data, so clearly it's not happy & only works if the tag has just <product> in it.

So I need a way of changing this text...

<Product xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd">

to this....

<Product>

Alternatively it looks like the text that my code is barfing over, actually may be of use....it appears to be a link to an .xsd .....perhaps I need to use that?)

 

[Cronk]

Not an XML expert but try

Dim nNode As MSXML2.IXMLDOMNode
Set nNode = testDom.getElementsByTagName("Amount").Item(0)
If IsEmpty(nNode.ChildNodes(0).Text) Then
   txtPrice = Null
Else
   txtPrice = nNode.ChildNodes(0).Text
End If

 

[peskywinnets]

Thanks for your input, but it errored on the line....

If IsEmpty(nNode.ChildNodes(0).Text) Then

 

[peskywinnets]

Anybody?

Let's see if I can expain this better!!

When the XML I'm want to parse is downloaded, it looks like this...

<GetCompetitivePricingForASINResult[COLOR="Red"] ASIN="B002J6HJAA" status="Success"[/COLOR]>
<Product[COLOR="Red"] xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01" xmlns:ns2="http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd"[/COLOR]>
<Identifiers>
<MarketplaceASIN>

the XML continues, but cut here for ease of getting my point across....

....the code I've used from the net doesn't like those bits in RED, if I edit the XML manually & strip out the red text so it reads like this....

<GetCompetitivePricingForASINResponse>
<GetCompetitivePricingForASINResult>
<Product>
<Identifiers>
<MarketplaceASIN>
<MarketplaceId>A1F83G8C2ARO8P</MarketplaceId>
<ASIN>B002J6HJAA</ASIN>

the XML continues, but cut here for ease of getting my point across....

Then the XML file gets parsed just fine.

Here's the small bit of code I'm using to read in the XML....

Public Function GetCompetitivePrice() 'Reference Microsoft XML 6.0 required
     
    Dim testDOM As DOMDocument60
    Dim objRoot As IXMLDOMElement
    Dim strMsg As String
    Dim row As String
     
    Set testDOM = New DOMDocument60
    testDOM.resolveExternals = False
    testDOM.validateOnParse = False
    testDOM.Load ("C:\test.xml")
     
    Set objRoot = testDOM.documentElement
    
   
    ASIN = objRoot.selectSingleNode("/GetCompetitivePricingForASINResponse/GetCompetitivePricingForASINResult/Product/Identifiers/MarketplaceASIN/ASIN").text
    LowestPrice = objRoot.selectSingleNode("/GetCompetitivePricingForASINResponse/GetCompetitivePricingForASINResult/Product/CompetitivePricing/CompetitivePrices/CompetitivePrice/Price/LandedPrice/Amount").text
    NoOfSellers = objRoot.selectSingleNode("/GetCompetitivePricingForASINResponse/GetCompetitivePricingForASINResult/Product/CompetitivePricing/NumberOfOfferListings/OfferListingCount[1]").text
    
    For Each n In testDOM.selectNodes("/GetCompetitivePricingForASINResponse/GetCompetitivePricingForASINResult/Product/CompetitivePricing/CompetitivePrices/CompetitivePrice")
    MyPriceWinning = n.Attributes.getNamedItem("belongsToRequester").text
    i = i + 1
    Next
 
       
    'Debug.Print "ASIN,LowestPrice,MyPriceWinning,NoOfSellers,Position"
    
    row = ASIN + "," + LowestPrice + "," + MyPriceWinning + "," + NoOfSellers
    Debug.Print row
     
End Function

I'm figuring I need to add something in to tell VBA about those lines causing a problem?

 

[Cronk]

Does your HTML file have a tag 'Amount' and does it have child nodes?

Provide your test.xml file

 

[peskywinnets]

Thanks for the reply - I simply want to extract the data marked in red from the XML which looks like this...

<GetCompetitivePricingForASINResponse xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01">
<GetCompetitivePricingForASINResult ASIN="B002L7HJAA" 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>A1F83G9C2ARO7P</MarketplaceId>
<ASIN>B002L7HJAA</ASIN>
</MarketplaceASIN>
</Identifiers>
<CompetitivePricing>
<CompetitivePrices>
<CompetitivePrice belongsToRequester="true" condition="New" subcondition="New">
<CompetitivePriceId>1</CompetitivePriceId>
<Price>
<LandedPrice>
<CurrencyCode>GBP</CurrencyCode>
<Amount>[COLOR="Red"]14.84[/COLOR]</Amount>
</LandedPrice>
<ListingPrice>
<CurrencyCode>GBP</CurrencyCode>
<Amount>14.84</Amount>
</ListingPrice>
<Shipping>
<CurrencyCode>GBP</CurrencyCode>
<Amount>0.00</Amount>
</Shipping>
</Price>
</CompetitivePrice>
</CompetitivePrices>
<NumberOfOfferListings>
<OfferListingCount condition="New">35</OfferListingCount>
<OfferListingCount condition="Any">35</OfferListingCount>
</NumberOfOfferListings>
</CompetitivePricing>
<SalesRankings>
<SalesRank>
<ProductCategoryId>home_improvement_display_on_website</ProductCategoryId>
<Rank>7553</Rank>
</SalesRank>
<SalesRank>
<ProductCategoryId>home_and_garden_display_on_website</ProductCategoryId>
<Rank>37404</Rank>
</SalesRank>
<SalesRank>
<ProductCategoryId>1938969031</ProductCategoryId>
<Rank>96</Rank>
</SalesRank>
</SalesRankings>
</Product>
</GetCompetitivePricingForASINResult>
<ResponseMetadata>
<RequestId>821a33fc-bcb6-4489-ac8d-d51179e4dbf0</RequestId>
</ResponseMetadata>
</GetCompetitivePricingForASINResponse>

I've since found a 100% related stackoverflow question that has been answered (& it's exactly the same XML data ...plus the OP asking wants exactly the same piece of data extracted), *but* I couldn't make head nor tail out of the replies he received(!), here it is...

http://stackoverflow.com/questions/...xpath-query-from-xml-with-multiple-namespaces