XML Node lists and VBA (1 Viewer)

[Cody.K]

As proof of concept, I'm trying to set up a simple database to work with a UPC database API. This involves XML, which isn't something I'm very familiar with.

This is what I have so far:

Private Sub txtUPC_Change()
    Dim strURL As String
    Dim oReq As Object
    Set oReq = CreateObject("MSXML2.XMLHTTP")
    Dim oDOM As Object
    Set oDOM = CreateObject("MSXML2.DOMDocument.3.0")
    oDOM.async = "false"
    Dim oNodeList As IXMLDOMNodeList
    
    Dim strUPC, strResult As String, strItem As String, strAlias As String, strDescription As String, stravgprice As String

                    
    On Error GoTo ErrRoutine
    
    strUPC = Me!txtUPC
    strURL = "http://api.upcdatabase.org/xml/239eed2d71404ba7d503bfaa81cac86e/" & strUPC
    oReq.Open "GET", strURL, False
    oReq.send
    oDOM.loadXML (oReq.responseText)
    Set oReq = Nothing
    
    Set oNodeList = oDOM.getElementsByTagName("valid")
        If oNodeList.length = 0 Then
            MsgBox ("Invalid UPC! Is there another barcode on the item?")
        GoTo EndRoutine
    End If

    Set oNodeList = oDOM.getElementsByTagName("itemname")
        'If oNodeList.length = 0 Then
            'MsgBox ("This item wasn't found in the upcdatabase.org database. In the future, we will be searching three online databases, and offering a means to add products.")
        'GoTo EndRoutine
    'End If
    
    strItem = oNodeList.Item(0).Text
    Me!txtItemName = StrConv(strItem, 3)
    
    Set oNodeList = oDOM.getElementsByTagName("alias")
        'If oNodeList.length = 0 Then
            'MsgBox ("No Alias found in the upcdatabase.org database. In the future, we will be searching three online databases, and offering a means to add products.")
        'GoTo EndRoutine
    'End If
    
    strAlias = oNodeList.Item(0).Text
    Me!txtDescription = strAlias
    
    Set oNodeList = oDOM.getElementsByTagName("description")
        'If oNodeList.length = 0 Then
            'MsgBox ("No description found in the upcdatabase.org database. In the future, we will be searching three online databases, and offering a means to add products.")
        'GoTo EndRoutine
    'End If
    
    strDescription = oNodeList.Item(0).Text
    Me!Text13 = strDescription
    
    Set oNodeList = oDOM.getElementsByTagName("avgprice")
        'If oNodeList.length = 0 Then
            'MsgBox ("An avg price wasn't found in the upcdatabase.org database. In the future, we will be searching three online databases, and offering a means to add products.")
        'GoTo EndRoutine
    'End If
    
    stravgprice = oNodeList.Item(0).Text
    Me!MSRP = stravgprice
    
    
EndRoutine:
    Exit Sub
    
ErrRoutine:
    MsgBox Err.Number & " - " & Err.Description, _
        vbOKOnly Or vbCritical, _
        "TestXMLHTTP"
    GoTo EndRoutine
End Sub

This is the API's Output if the UPC exists:

<?xml version="1.0" encoding="ISO-8859-1"?>
<output xmlns="http://upcdatabase.org/">
    <valid>true</valid>
    <number>0111222333446</number>
    <itemname>UPC Database Testing Code</itemname>
    <alias>Testing Code</alias>
    <description>http://upcdatabase.org/code/0111222333446</description>
    <avgprice>123.45</avgprice>
    <rate_up>2</rate_up>
    <rate_down>0</rate_down>
</output>

And if it doesn't:

<?xml version="1.0" encoding="ISO-8859-1"?>
<output xmlns="http://upcdatabase.org/">
<valid>false</valid>
<error>301</error>
<reason>Code does not exist!</reason>
</output>

Now, my code works in that it's pulling records from the API, but they aren't pulling in properly- for instance, "Item name" is sometimes placed in the "description", and in some instances it simply doesn't populate at all- and I suspect this has something to do with my node list item numbering [oNodeList.Item(0)].

I've attached a copy of the database and was wondering if someone wouldn't mind straightening me out. If you could explain it to me, it would be a plus.

Thanks in Advance