Parsing Quickbooks Customer (CustomerRet) Response using XML (DOMDocument)

As I indicated in my Quicbbooks Part 1 of never post, I would post some Microsoft Access and other Quickbooks source code and blog entries, rather than do a more detailed step by step explanation.

I hope this is helpful.

Quick Explanation

You can parse Quickbooks response using a ICustomerRetList – and I may post that code later – BUT it is less forgiving for missing data, requiring error checking to catch that. Using XML and the DOMDocument object lets me act on data returned and also navigate to childNodes when they exist.

This routine returns all active customers and then parses the returned XML. There is a call to a sub-routine that runs for each returned record. I don’t include that sub-routine. It simply checks to see if the customer data exist in my client’s SQL Server.

If the record already exist, based on the Quickbooks customer ListID, it updates it. If it does not exist, it creates the matching record in the client’s customer database. What you do with each returned customer record would, of course, be based on your client’s underlying data.

Function CustomerUsingXML()
Dim smgr As QBSessionManager
Set smgr = New QBSessionManager

smgr.OpenConnection "", "Sync w QB"
smgr.BeginSession "", omDontCare
'Dim qresp As IResponse
'Dim rList As IResponseList
Dim rMsg As IMsgSetRequest
Dim rMsgr As IMsgSetResponse

Dim xCust As DOMDocument60
Set xCust = New DOMDocument60

Set rMsg = smgr.CreateMsgSetRequest("US", 13, 0)


Set jl = rMsg.AppendCustomerQueryRq
jl.ORCustomerListQuery.CustomerListFilter.ActiveStatus.SetValue 0
Set rMsgr = smgr.DoRequests(rMsg)
strCustXML = rMsgr.ToXMLString()
xCust.loadXML strCustXML  'load the XML into your document


'get the customer nodes
Set cust = xCust.selectNodes("//CustomerRet")
xct = cust.length

'parse through all the customers
For x = 0 To xct - 1
    strListID = ""
    strEditSeq = ""
    strTimeCreated = ""
    strTimeModified = ""
    
    strFullname = ""
    strName = ""
    strFirstname = ""
    strLastname = ""
    strEmailaddr = ""
    strURL = ""
    
    'each data element is a child node
    For y = 0 To (cust.Item(x).childNodes.length - 1)
        Select Case cust.Item(x).childNodes(y).nodeName
            Case "ListID"
            strListID = cust.Item(x).childNodes(y).Text
            Case "EditSequence"
            strEditSeq = cust.Item(x).childNodes(y).Text
            Case "TimeCreated"
            strTimeCreated = cust.Item(x).childNodes(y).Text
            Case "TimeModified"
            strTimeModified = cust.Item(x).childNodes(y).Text
            Case "FullName"
            strFullname = cust.Item(x).childNodes(y).Text
            Case "Name"
            strName = cust.Item(x).childNodes(y).Text
            Case "Firstname"
            strFirstname = cust.Item(x).childNodes(y).Text
            Case "Lastname"
            strLastname = cust.Item(x).childNodes(y).Text
            Case "Email"
            strEmailaddr = cust.Item(x).childNodes(y).Text
            
            Case "AdditionalContactRef" 'pay attention kids - some knowledge of the returned XML is necessary
                ' I could more careful iterate through these nodes to derive any data.. but..
                
                If cust.Item(x).childNodes(y).childNodes(0).Text = "Website" Then
                    strURL = cust.Item(x).childNodes(y).childNodes(1).Text
                End If
                    
            
            Case Else
            
        End Select
    
    Next
' THIS IS THE SUB ROUTINE THAT CHECKS FOR THE EXISTENCE OF THE QUICKBOOKS LISTID IN MY CLIENT'S DATABASE AND UPDATES OR CREATES IT AS NEEDED
    UpdateCustomerRec strListID, strTimeCreated, strTimeModified, strEditSeq, strFullname, strName, strFirstname, strLastname, strEmail, strURL

Next



strResponse = rMsgr.ToXMLString
'FOR TESTING. I HAVE A FUNCTION THAT CREATES A FILE. I USE THIS TO TEST WHAT IS BEING RETURNED FROM QUICKBOOKS
'textresponse.Value = strResponse
'chc = MakeFile(strResponse, "c:\data\mycust.xml")
smgr.EndSession
smgr.CloseConnection
CustomerUsingXML = strResponse
End Function

Hopefully this is helpful and gives you a clue on how to move your project forward.

If so, leave me a comment and consider sharing this.

Thanks.