Reputation: 132
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.
Upvotes: 0
Views: 2184
Reputation: 3475
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
Upvotes: 1