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.

AHeyne · Accepted Answer · 2019-02-01 16:57:59Z

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

edited Feb 1, 2019 at 16:57

answered Jan 31, 2019 at 17:17

AHeyne

3,5052 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dbWizard Over a year ago

Sorry, I thought I'd included an example. This is basically what my file format looks like: <n0:PurchaseOrderRequest> <PurchaseOrder> <PONumber>1234</PONumber> <Customer>ABC Building Supply</Customer> <Item> <ID>1</ID> <ITEM_QTY>3</ITEM_QTY> 'several other nodes here </Item> <Item> <ID>2</ID> <ITEM_QTY>3</ITEM_QTY> 'several other nodes here </Item> </PurchaseOrder> </n0:PurchaseOrderRequest>

dbWizard Over a year ago

What I want to do is something like pass the <PurchaseOrder> node into a subroutine with code that looks something like: ReadNodes xNode.SelectSingleNode("/n0:PurchaseOrderRequest/").FirstChild but that returns an error: Expression does not return a dom node And then run code similar to yours which reads the PONumber and Customer, and then loops through the <Item> nodes to

dbWizard Over a year ago

Thanks for your help. I came up with my own solution, very similar to yours, while I was waiting for a reply.

AHeyne Over a year ago

Fine! Thats the common problem with time shifts. I was sleeping. ;-)

Collectives™ on Stack Overflow

Read XML file with VBA with multiple <Item> nodes

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related