Read XML file with VBA with multiple <Item> nodes

Question

I am attempting to read an XML file and load the data into an Access database. I've identified the nodes I need to read, and assigned them to fields in my database, but am having difficulty reading the actual values. The following is a map of the database fields to the node names:

XL_ColumnName  XML_NodePathAndName
Customer       /n0:PurchaseOrderRequest/PurchaseOrder/BuyerParty/Address/OrganisationFormattedName
PO             /n0:PurchaseOrderRequest/PurchaseOrder/ID
Qty            /n0:PurchaseOrderRequest/PurchaseOrder/Item/n1:ZZHL_ITEM_QTY
DueDate        /n0:PurchaseOrderRequest/PurchaseOrder/Item/n1:ZZSHIPDATE
Price-Each     /n0:PurchaseOrderRequest/PurchaseOrder/Item/Price/NetUnitPrice/Amount
Part_Number    /n0:PurchaseOrderRequest/PurchaseOrder/Item/Product/BuyerID
Description    /n0:PurchaseOrderRequest/PurchaseOrder/Item/Product/Note
Ship To Zip    /n0:PurchaseOrderRequest/PurchaseOrder/Item/ShipToLocation/Address/PhysicalAddress/StreetPostalCode

At the moment, the code I'm using to read a single node looks like:

Public Function ProcessXML(frm As Form)

    Dim xdoc As MSXML2.DOMDocument
    Dim xNode As MSXML2.IXMLDOMNode
    Dim Nodes As MSXML2.IXMLDOMNodeList

    Dim strFilename As String

    Set xdoc = New MSXML2.DOMDocument

    'Load the XML document
    xdoc.validateOnParse = False
    strFilename = frm.txt_Filename
    xdoc.Load (strFilename)

    For Each xNode In xdoc.ChildNodes

        If xNode.NodeType = 7 Then
            'this is "xml", ignore it
        Else

            ReadNodesEx1 xNode

        End If

    Next

End Function

Public Sub ReadNodesEx1(ByRef Node As MSXML2.IXMLDOMNode)

Dim strSQL As String
Dim strValue As String
Dim rs As DAO.Recordset
Dim Nodes As MSXML2.IXMLDOMNodeList

On Error GoTo ProcError

strSQL = "SELECT XML_NodePathAndName, Staging_ColumnName FROM tbl_XML_Schema WHERE Staging_ColumnName IS NOT NULL"
Set rs = GetDB.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError)

While Not rs.EOF

    strValue = Nz(Node.SelectSingleNode(rs!XML_NodePathAndName).Text, "NULL")
    Debug.Print rs!Staging_ColumnName, rs!XML_NodePathAndName, strValue

NextField:
        rs.MoveNext
    Wend

ProcExit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Exit Sub

ProcError:
    If Err.Number = 91 Then
        Debug.Print rs!Staging_ColumnName, rs!XML_NodePathAndName, "ITEM NOT FOUND"
        Resume NextField
    Else
        MsgBox Err.Number & vbCrLf & Err.Description, , "ReadNodesEX1"
        Debug.Print "ReadNodesEX1", Err.Number, Err.Description
        Resume ProcExit
        Resume
    End If
End Sub

It does fine at reading a single node, but there could be more than one item in a purchase order, in which case the Customer, and Purchase Order values will be the same for all Items. What I cannot figure out is how to resolve this so that I can loop through the recordset node names multiple times, until I've reached the last Item in the Item collection.

I'm sure this involves some form of loop through the Items collection, but I'm having difficulty crafting the VBA to loop through all of the XML "Item" nodes and through all of the records in the recordset which define the specific nodes to read.

Answer 1

Edit: Now having your sample XML:

<?xml version="1.0"?>
<PurchaseOrderRequest>
    <PurchaseOrder>
        <PONumber>1234</PONumber>
        <Customer>ABC Building Supply</Customer>
        <Item>
            <ID>1</ID>
            <ITEM_QTY>3</ITEM_QTY>
        </Item>
        <Item>
            <ID>2</ID>
            <ITEM_QTY>3</ITEM_QTY>
        </Item>
    </PurchaseOrder>
</PurchaseOrderRequest>

With this code you can iterate and work on all you described:

Sub ReadPurchases()
    Dim purchaseOrderRequest As MSXML2.DOMDocument
    Set purchaseOrderRequest = New MSXML2.DOMDocument

    purchaseOrderRequest.validateOnParse = True
    purchaseOrderRequest.SetProperty "SelectionLanguage", "XPath"

    purchaseOrderRequest.Load "z:\purchaseOrderRequest.xml"

    Dim purchaseOrder As IXMLDOMNode
    For Each purchaseOrder In purchaseOrderRequest.selectNodes("PurchaseOrderRequest/PurchaseOrder")
        Debug.Print "PONumber:", purchaseOrder.selectSingleNode("PONumber").Text
        Debug.Print "Customer:", purchaseOrder.selectSingleNode("Customer").Text
        ReadPurchaseOrderItems purchaseOrder.selectNodes("Item")
    Next
End Sub

Sub ReadPurchaseOrderItems(ByVal purchaseOrderItems As MSXML2.IXMLDOMNodeList)
    Dim purchaseOrderItem As IXMLDOMNode
    For Each purchaseOrderItem In purchaseOrderItems
        Debug.Print "  ID:", purchaseOrderItem.selectSingleNode("ID").Text
        Debug.Print "  ITEM_QTY:", purchaseOrderItem.selectSingleNode("ITEM_QTY").Text
    Next
End Sub

The output will be:

PONumber:     1234
Customer:     ABC Building Supply
  ID:         1
  ITEM_QTY:   3
  ID:         2
  ITEM_QTY:   3