Solved

Node problem with Import XML to access

Posted on 2011-09-02
8
368 Views
Last Modified: 2012-06-27
I have been working on this for a while.  Trying import same payroll data to create checks.  I got the DOM to work and i get get the rows in, but i can't make it loop at the right place.

You will see in the xml (sample data) that the the <CheckAdd> has the nodes of the checks themselves, the  <ExpenseLineAdd> has the lines items for the checks.   The ms access code (originally from LPurvis:) almost works, but it cant seem to see where <ExpenseLineAdd> starts.  So it loops on employees, but does not separate line items from the body of the check.

See access code below xml.  Thanks
<EmployeeAddRq>
 <EmployeeAdd>
   <Name>Mike Black</Name>
   <FirstName>Mike</FirstName>
   <MiddleName/>
   <LastName>Black</LastName>
   <EmployeeID>11277166</EmployeeID>
   <EmployeeAddress>
     <Addr1>101 N Sunny Way</Addr1>
     <Addr2/>
     <City>Decatur</City>
     <State>GA</State>
     <PostalCode>30033</PostalCode>
   </EmployeeAddress>
   <PrintAs>Mike Black</PrintAs>
   <Email>edale@samplecompany.com</Email>
   <HiredDate>2001-03-19</HiredDate>
   <WorkLocation>
      <WorkLocationID>11364485</WorkLocationID>
      <Addr1>9999 FancySprings Rd</Addr1>
      <Addr2>Suite A</Addr2>
      <City>Norcross</City>
      <State>GA</State>
      <PostalCode>30071</PostalCode>
   </WorkLocation>
 </EmployeeAdd>
</EmployeeAddRq>

Open in new window


Function fImportXML(strXML As String, strTableName As String, strPKey As String)

    Dim xmlDOM As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim rst As DAO.Recordset
    Dim strDelim As String
    Dim blnPK As Boolean
    Dim blnAppend As Boolean
    Dim intIndex As Integer
   
    Debug.Print Dir(strXML)
   
    Set xmlDOM = New DOMDocument
   
    
    If xmlDOM.Load(strXML) Then
    Debug.Print "doc loaded"
    Else
    Debug.Print "nope"
    End If
   
      
       Set xmlNodeItem = xmlDOM.documentElement.childNodes.Item(0)
             
            Debug.Print xmlNodeItem.childNodes.length
            Debug.Print xmlNodeItem.childNodes(0).Text
            Debug.Print xmlNodeItem.childNodes(1).Text
   
     If Err.Number = 91 Then
        Debug.Print "error 91"
    Else
        Debug.Print "no error 91"
        
    End If
      Set xmlNodeList = xmlDOM.getElementsByTagName("CheckAdd")
      

    For Each xmlNodeItem In xmlNodeList
    
         Debug.Print "xmlNodeItem: " & xmlNodeItem.Text
         Debug.Print "xmlNodeItem: " & xmlNodeItem.nodeName
         
         
         
            For Each xmlNodeField In xmlNodeItem.childNodes
                Debug.Print "  xmlNodefield " & xmlNodeField.Text
                
                If xmlNodeField.hasChildNodes Then
                    Debug.Print "     has child"
                    Debug.Print "     xmlNodeField.childNodes(0): " & xmlNodeField.childNodes(0).Text
                    Debug.Print "     xmlNodeField.childNodes(0): " & xmlNodeField.childNodes(0).nodeName
                    'Debug.Print xmlNodeField.childNodes(2).Text
                    
                    Else
                    Debug.Print "no child"
                    End If
                    
                
            Next
            
        Next

Open in new window

0
Comment
Question by:pressMac
  • 4
  • 3
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36477007
pressMac,

It looks like the code was designed at the CheckAdd level to look for child nodes and then display text value. The issue is for the ExpenseLineAdd there is no text, but rather more child nodes. You just need another IF to check if xmlNodeField.childNodes(n).hasChildNodes.

Kevin
0
 

Author Comment

by:pressMac
ID: 36510073
Hi mwvisa1,

was on holiday - so thanks for your response.

I updated the code as shown.  It now loops through to the lowest level.  I added another level for ForNext

I now have to work some logic to create the main check <CheckADdd>, and then the check sub items <ExpenseLineAdd>, unfortunately, the first <ExpenseLineAdd> is really the next amount of the rest of the line items. .  

So thanks for the help so far!
Function fImportXML(strXML As String, strTableName As String, strPKey As String)

    Dim xmlDOM As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim xmlNodeSubItem As IXMLDOMNode
    Dim rst As DAO.Recordset
    Dim strDelim As String
    Dim blnPK As Boolean
    Dim blnAppend As Boolean
    Dim intIndex As Integer
   
    Debug.Print Dir(strXML)
   
    Set xmlDOM = New DOMDocument
   
    
    If xmlDOM.Load(strXML) Then
    Debug.Print "doc loaded"
    Else
    Debug.Print "nope"
    End If
   
      
      ' Set xmlNodeItem = xmlDOM.documentElement.childNodes.Item(0)
             
      ''      Debug.Print "xmlNodeItem.childNodes.length-" & xmlNodeItem.childNodes.length
      '      Debug.Print "xmlNodeItem.childNodes(0).Text-" & xmlNodeItem.childNodes(0).Text
       '     Debug.Print "xmlNodeItem.childNodes(1).Text-" & xmlNodeItem.childNodes(1).Text
   
     If Err.Number = 91 Then
        Debug.Print "error 91"
    Else
        Debug.Print "no error 91"
        
    End If
      Set xmlNodeList = xmlDOM.getElementsByTagName("CheckAdd")
      

    For Each xmlNodeItem In xmlNodeList
    
         Debug.Print "xmlNodeItem.Text: " & xmlNodeItem.Text
         Debug.Print "xmlNodeItem.nodeName: " & xmlNodeItem.nodeName
          Debug.Print "xmlNodeItem.baseName: " & xmlNodeItem.baseName
               Debug.Print "xmlNodeItem.hasChildNodes: " & xmlNodeItem.hasChildNodes
               Debug.Print "xmlNodeItem.nodeTypedValue: " & xmlNodeItem.nodeTypedValue
               Debug.Print "xmlNodeItem.nodeType: " & xmlNodeItem.nodeType
               Debug.Print "xmlNodeItem.nodeTypeString: " & xmlNodeItem.nodeTypeString
               Debug.Print "xmlNodeItem.nodeValu: " & xmlNodeItem.nodeValue
               Debug.Print "xmlNodeItem.parsed: " & xmlNodeItem.parsed
               Debug.Print "xmlNodeItem.prefix: " & xmlNodeItem.prefix
               Debug.Print "xmlNodeItem.specified: " & xmlNodeItem.specified
               
         
         
            For Each xmlNodeField In xmlNodeItem.childNodes
                Debug.Print "  CHILD NODE xmlNodefield.Text:  " & xmlNodeField.Text
                Debug.Print "  CHILD NODE xmlNodefield.nodeName:  " & xmlNodeField.nodeName
                Debug.Print "  CHILD NODE xmlNodefield.hasChildNodes:  " & xmlNodeField.hasChildNodes
           
                    For Each xmlNodeSubItem In xmlNodeField.childNodes
                         Debug.Print "     CHILD CHILD NODE xmlNodeSubItem.Text:  " & xmlNodeSubItem.Text
                         Debug.Print "     CHILD CHILD NODE xmlNodeSubItem.nodeName:  " & xmlNodeSubItem.nodeName
                         Debug.Print "     CHILD CHILD NODE xmlNodeSubItem.hasChildNodes:  " & xmlNodeSubItem.hasChildNodes
                    
                        
                             
                         
                     Next
                    
                
            Next
            
        Next
    
    

    
   
End Function

Open in new window

0
 

Author Comment

by:pressMac
ID: 36510720
Getting closer.

Have trouble getting the rstCheckLineItems.Update to fire only on <ExpenseLineAdd> instead of every child node


Option Compare Database

Function fImportXML(strXML As String, strTableName As String, strPKey As String)

    Dim xmlDOM As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim xmlNodeSubItem As IXMLDOMNode
    Dim rst As DAO.Recordset
    Dim strDelim As String
    Dim blnPK As Boolean
    Dim blnAppend As Boolean
    Dim intIndex As Integer
   
    Debug.Print Dir(strXML)
   
    Set xmlDOM = New DOMDocument
   
    
    If xmlDOM.Load(strXML) Then
    Debug.Print "doc loaded"
    Else
    Debug.Print "nope"
    End If
   
      
      ' Set xmlNodeItem = xmlDOM.documentElement.childNodes.Item(0)
             
      ''      Debug.Print "xmlNodeItem.childNodes.length-" & xmlNodeItem.childNodes.length
      '      Debug.Print "xmlNodeItem.childNodes(0).Text-" & xmlNodeItem.childNodes(0).Text
       '     Debug.Print "xmlNodeItem.childNodes(1).Text-" & xmlNodeItem.childNodes(1).Text
   
     If Err.Number = 91 Then
        Debug.Print "error 91"
    Else
        Debug.Print "no error 91"
        
    End If
      Set xmlNodeList = xmlDOM.getElementsByTagName("CheckAdd")
      
Debug.Print "Setup check recordset"

    For Each xmlNodeItem In xmlNodeList
Debug.Print "rstChecks.add"
         Debug.Print "xmlNodeItem.Text: " & xmlNodeItem.Text
         Debug.Print "xmlNodeItem.nodeName: " & xmlNodeItem.nodeName
          Debug.Print "xmlNodeItem.baseName: " & xmlNodeItem.baseName
               Debug.Print " xmlNodeItem.hasChildNodes: " & xmlNodeItem.hasChildNodes
               Debug.Print " xmlNodeItem.nodeTypedValue: " & xmlNodeItem.nodeTypedValue
               Debug.Print " xmlNodeItem.nodeType: " & xmlNodeItem.nodeType
               Debug.Print " xmlNodeItem.nodeTypeString: " & xmlNodeItem.nodeTypeString
               Debug.Print " xmlNodeItem.nodeValu: " & xmlNodeItem.nodeValue
               Debug.Print " xmlNodeItem.parsed: " & xmlNodeItem.parsed
               Debug.Print " xmlNodeItem.prefix: " & xmlNodeItem.prefix
               Debug.Print " xmlNodeItem.specified: " & xmlNodeItem.specified
               
               For Each xmlNodeField In xmlNodeItem.childNodes
                'Debug.Print "  CHILD NODE xmlNodefield.Text:  " & xmlNodeField.Text
                'Debug.Print "  CHILD NODE xmlNodefield.nodeName:  " & xmlNodeField.nodeName
                'Debug.Print "  CHILD NODE xmlNodefield.hasChildNodes:  " & xmlNodeField.hasChildNodes
           

                   If xmlNodeField.nodeName = "PaymentID" Then
                   Debug.Print "  rstChecks!PaymentID" & xmlNodeField.Text
                   Else
                   End If
                   
                   If xmlNodeField.nodeName = "PayeeEntityRef" Then
                        Debug.Print "  rstChecks!PayeeEntityRef" & xmlNodeField.Text
                   Else
                   End If
                   
                    If xmlNodeField.nodeName = "TxnDate" Then
                        Debug.Print "  rstChecks!TxnDate" & xmlNodeField.Text
                   Else
                   End If
                   
                   If xmlNodeField.nodeName = "Memo" Then
                        Debug.Print "  rstChecks!Memo" & xmlNodeField.Text
                   Else
                   End If
                   
                   
                    If xmlNodeField.nodeName = "ExpenseLineAdd" Then
                       
                        Debug.Print "<ExpenseLineAdd>" & xmlNodeField.Text; ""
                   Else
                   End If
                
                    For Each xmlNodeSubItem In xmlNodeField.childNodes
                         
                                If xmlNodeSubItem.nodeName = "AccountRef" Then
                                    Debug.Print "  Add Line AccountRef" & xmlNodeSubItem.Text
                                Else
                                End If
                         
                                If xmlNodeSubItem.nodeName = "Amount" Then
                                    Debug.Print "  Add Line Amount" & xmlNodeSubItem.Text
                                Else
                                End If
                                
                                If xmlNodeSubItem.nodeName = "Memo" Then
                                    Debug.Print "  Add Line Memo NOT USED" & xmlNodeSubItem.Text
                                Else
                                End If
                                
                                Debug.Print "rstCheckLineItems.Update"
                           
                     Next
                   
                     
                   
                
            Next
           Debug.Print "rstChecks.Update"
                    
        Next
    
    

    
   
End Function

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36510747
So are you just needing to exclude the first child?
Can you re-post the XML for testing purposes, please?
I will be in meetings in about 30 minutes from now until for about 4 hours. Therefore, if I do not respond right away, you will know why.
0
 

Author Comment

by:pressMac
ID: 36510877
XML repost
<?xml version="1.0" ?>
<?pcxml version="2.0" ?>
<PCXML>

<Company>
<ID>55-5555555</ID>
<BusinessName>Test Company Name, Inc.</BusinessName>
</Company>
<AccountAddRq>
<AccountAdd>
<Name>221</Name>
<AccountType>Bank</AccountType>
<Desc>Checking Account - Payroll</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>1-4000</Name>
<AccountType>OtherCurrentAsset</AccountType>
<Desc>Asset Account - Employee Deductions</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>7-2253</Name>
<AccountType>Expense</AccountType>
<Desc>Tax Account - Employer Taxes</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>2-3600</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Employee Deductions</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>2-3000</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Total Taxes</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>6-3001</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Employee Deductions</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>6-3000</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Employee Deductions</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>6-8550</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Employee Deductions</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>2-3120</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Total Taxes</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>2-3160</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Total Taxes</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>2-3171</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Total Taxes</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>2-3170</Name>
<AccountType>OtherCurrentLiability</AccountType>
<Desc>Payee Account - Total Taxes</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>6-1020</Name>
<AccountType>Expense</AccountType>
<Desc>Wage Account - Employee Gross Pay</Desc>
</AccountAdd>
</AccountAddRq>
<EmployeeAddRq>
<EmployeeAdd>
<Name>Fred M. Thompson</Name>
<FirstName>Fred</FirstName>
<MiddleName>M</MiddleName>
<LastName>Thompson</LastName>
<EmployeeID>14405230</EmployeeID>
<EmployeeAddress>
<Addr1>3150 Pine Isle Rd</Addr1>
<Addr2></Addr2>
<City>Orlando</City>
<State>FL</State>
<PostalCode>34754</PostalCode>
</EmployeeAddress>
<PrintAs>Fred M. Thompson</PrintAs>
<Email>fredaa@testcompany.com</Email>
<HiredDate>2009-11-09</HiredDate>
<WorkLocation>
<WorkLocationID>11364486</WorkLocationID>
<Addr1>8440 Seaport Dr</Addr1>
<Addr2>Suite 107</Addr2>
<City>Orlando</City>
<State>FL</State>
<PostalCode>32999</PostalCode>
</WorkLocation>
</EmployeeAdd>
</EmployeeAddRq>
<CheckAddRq>
<CheckAdd>
<PaymentID>33736635</PaymentID>
<AccountRef><FullName>221</FullName></AccountRef>
<PayeeEntityRef><FullName>Fred M. Thompson</FullName></PayeeEntityRef>
<RefNumber></RefNumber>
<DirectDeposit>false</DirectDeposit>
<TxnDate>2011-09-02</TxnDate>
<Memo>Pay Period: 08/14/2011-08/27/2011 </Memo>
<IsToBePrinted>false</IsToBePrinted>
<ExpenseLineAdd>
<AccountRef><FullName>6-1020</FullName></AccountRef>
<Amount>2500.00</Amount>
<Memo>Gross Pay - This is not a legal paystub</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>7-2253</FullName></AccountRef>
<Amount>191.25</Amount>
<Memo>Employer Taxes</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3171</FullName></AccountRef>
<Amount>0.00</Amount>
<Memo>FL Unemployment Tax</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3160</FullName></AccountRef>
<Amount>0.00</Amount>
<Memo>Federal Unemployment (940)</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3000</FullName></AccountRef>
<Amount>-681.54</Amount>
<Memo>Federal Taxes (941/944)</Memo>
</ExpenseLineAdd>
</CheckAdd>
</CheckAddRq>
<EmployeeAddRq>
<EmployeeAdd>
<Name>Vernon Smith</Name>
<FirstName>Vernon</FirstName>
<MiddleName></MiddleName>
<LastName>Smith</LastName>
<EmployeeID>11277166</EmployeeID>
<EmployeeAddress>
<Addr1>101 Oak Way</Addr1>
<Addr2></Addr2>
<City>Decatur</City>
<State>GA</State>
<PostalCode>30033</PostalCode>
</EmployeeAddress>
<PrintAs>Vernon Smith</PrintAs>
<Email>edale@testcompany.com</Email>
<HiredDate>2001-03-19</HiredDate>
<WorkLocation>
<WorkLocationID>11364485</WorkLocationID>
<Addr1>2000 Ocean Springs Rd</Addr1>
<Addr2>Suite A</Addr2>
<City>Norcross</City>
<State>GA</State>
<PostalCode>30071</PostalCode>
</WorkLocation>
</EmployeeAdd>
</EmployeeAddRq>
<CheckAddRq>
<CheckAdd>
<PaymentID>33736636</PaymentID>
<AccountRef><FullName>221</FullName></AccountRef>
<PayeeEntityRef><FullName>Vernon Smith</FullName></PayeeEntityRef>
<RefNumber></RefNumber>
<DirectDeposit>false</DirectDeposit>
<TxnDate>2011-09-02</TxnDate>
<Memo>Pay Period: 08/14/2011-08/27/2011 </Memo>
<IsToBePrinted>false</IsToBePrinted>
<ExpenseLineAdd>
<AccountRef><FullName>6-1020</FullName></AccountRef>
<Amount>1661.54</Amount>
<Memo>Gross Pay - This is not a legal paystub</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>7-2253</FullName></AccountRef>
<Amount>120.24</Amount>
<Memo>Employer Taxes</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>6-3000</FullName></AccountRef>
<Amount>-89.86</Amount>
<Memo>Health Insurance PT - Employee Deduction</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3170</FullName></AccountRef>
<Amount>0.00</Amount>
<Memo>GA Unemployment Tax</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3120</FullName></AccountRef>
<Amount>-47.84</Amount>
<Memo>GA Income Tax</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3160</FullName></AccountRef>
<Amount>0.00</Amount>
<Memo>Federal Unemployment (940)</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3000</FullName></AccountRef>
<Amount>-245.56</Amount>
<Memo>Federal Taxes (941/944)</Memo>
</ExpenseLineAdd>
</CheckAdd>
</CheckAddRq>
<EmployeeAddRq>
<EmployeeAdd>
<Name>Robert K. Jones, Jr</Name>
<FirstName>Robert</FirstName>
<MiddleName>K</MiddleName>
<LastName>Jones, Jr</LastName>
<EmployeeID>11297028</EmployeeID>
<EmployeeAddress>
<Addr1>2803 Maple Dr</Addr1>
<Addr2></Addr2>
<City>Rex</City>
<State>GA</State>
<PostalCode>30273</PostalCode>
</EmployeeAddress>
<PrintAs>Robert K. Jones, Jr</PrintAs>
<Email>rJones@testcompany.com</Email>
<HiredDate>2007-09-04</HiredDate>
<WorkLocation>
<WorkLocationID>11364485</WorkLocationID>
<Addr1>2000 Ocean Springs Rd</Addr1>
<Addr2>Suite A</Addr2>
<City>Norcross</City>
<State>GA</State>
<PostalCode>30071</PostalCode>
</WorkLocation>
</EmployeeAdd>
</EmployeeAddRq>
<CheckAddRq>
<CheckAdd>
<PaymentID>33736637</PaymentID>
<AccountRef><FullName>221</FullName></AccountRef>
<PayeeEntityRef><FullName>Robert K. Jones, Jr</FullName></PayeeEntityRef>
<RefNumber></RefNumber>
<DirectDeposit>false</DirectDeposit>
<TxnDate>2011-09-02</TxnDate>
<Memo>Pay Period: 08/14/2011-08/27/2011 </Memo>
<IsToBePrinted>false</IsToBePrinted>
<ExpenseLineAdd>
<AccountRef><FullName>6-1020</FullName></AccountRef>
<Amount>1458.09</Amount>
<Memo>Gross Pay - This is not a legal paystub</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>7-2253</FullName></AccountRef>
<Amount>102.66</Amount>
<Memo>Employer Taxes</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>6-3000</FullName></AccountRef>
<Amount>-89.86</Amount>
<Memo>Health Insurance PT - Employee Deduction</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>6-3001</FullName></AccountRef>
<Amount>-15.17</Amount>
<Memo>Dental PT - Employee Deduction</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>6-3000</FullName></AccountRef>
<Amount>-11.12</Amount>
<Memo>Aflac - Employee Deduction</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3170</FullName></AccountRef>
<Amount>0.00</Amount>
<Memo>GA Unemployment Tax</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3120</FullName></AccountRef>
<Amount>-61.67</Amount>
<Memo>GA Income Tax</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3160</FullName></AccountRef>
<Amount>0.00</Amount>
<Memo>Federal Unemployment (940)</Memo>
</ExpenseLineAdd>
<ExpenseLineAdd>
<AccountRef><FullName>2-3000</FullName></AccountRef>
<Amount>-287.27</Amount>
<Memo>Federal Taxes (941/944)</Memo>
</ExpenseLineAdd>
</CheckAdd>
</CheckAddRq>



</PCXML>

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36513294
Hi.

I got a chance to look at this and it would appear that you may just need to embed this code:
For Each xmlNodeSubItem In xmlNodeField.childNodes
    If xmlNodeSubItem.nodeName = "AccountRef" Then
        Debug.Print "  Add Line AccountRef" & xmlNodeSubItem.Text
    End If
    
    If xmlNodeSubItem.nodeName = "Amount" Then
        Debug.Print "  Add Line Amount" & xmlNodeSubItem.Text
    End If
    
    If xmlNodeSubItem.nodeName = "Memo" Then
        Debug.Print "  Add Line Memo NOT USED" & xmlNodeSubItem.Text
    End If
    
    Debug.Print "rstCheckLineItems.Update"
Next

Open in new window


Under:
If xmlNodeField.nodeName = "ExpenseLineAdd" Then
    Debug.Print "<ExpenseLineAdd>" & xmlNodeField.Text; ""
End If

Open in new window


Taking note that with IF/THEN statements, you do not need to include an ELSE if you are not going to do an action outside the true condition. If you need some of the other code to work on all elements other than <ExpenseLineAdd>, then alternatively you can just wrap the "rstCheckLineItems.Update" bit with an IF/THEN by looking at xmlNodeSubItem.parentNode.nodeName.

So either:
If xmlNodeField.nodeName = "ExpenseLineAdd" Then
    Debug.Print "<ExpenseLineAdd>" & xmlNodeField.Text; ""
    
    For Each xmlNodeSubItem In xmlNodeField.childNodes
        ' ... other code omitted for brevity...
    
        Debug.Print "rstCheckLineItems.Update"
    Next
End If

Open in new window


Or:
For Each xmlNodeSubItem In xmlNodeField.childNodes
    ' ... other code omitted for brevity...    

    If xmlNodeSubItem.parentNode.nodeName = "ExpenseLineAdd" Then
        Debug.Print "rstCheckLineItems.Update"
    End If
Next

Open in new window


Hope that helps!
0
 

Author Comment

by:pressMac
ID: 36517647
Hey mwvisa1,

Thanks for the help.  Everything is look pretty good now.  I put the db write functions directly in the function instead of calling the.  Especially for the first level write (checks).  I needed to .update that so i could get the billID so i could foreign key the line items, but then need the sum of line items to put total in check.  The total will not come until all of the line items are processed, which of course is after needing the billid for those line items.  hence the two occurances of rstChecks.update.

I have posted the current code and awarded the points.   I will post again later when it is cleaned up.
this was my first go with XML.

Function fImportXML(strXML As String)

    Dim xmlDOM As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim xmlNodeSubItem As IXMLDOMNode
        
    Dim cnn As New ADODB.Connection
    Dim rstChecks As New ADODB.Recordset
    Dim rstPayablesLine As New ADODB.Recordset
    Dim sql As String
    Dim sqlPL As String
    Dim PayableID As Long
    

    Set cnn = CurrentProject.Connection
    sql = "Select * from Payables"
    sqlPL = "Select * from [Payables Items]"
   
    Debug.Print Dir(strXML)
   
    Set xmlDOM = New DOMDocument
   
    
    If xmlDOM.Load(strXML) Then
    Debug.Print "doc loaded"
    Else
    Debug.Print "nope"
    End If
   
      
      ' Set xmlNodeItem = xmlDOM.documentElement.childNodes.Item(0)
             
      ''      Debug.Print "xmlNodeItem.childNodes.length-" & xmlNodeItem.childNodes.length
      '      Debug.Print "xmlNodeItem.childNodes(0).Text-" & xmlNodeItem.childNodes(0).Text
       '     Debug.Print "xmlNodeItem.childNodes(1).Text-" & xmlNodeItem.childNodes(1).Text
   
     If Err.Number = 91 Then
        Debug.Print "error 91"
    Else
        Debug.Print "no error 91"
        
    End If
      Set xmlNodeList = xmlDOM.getElementsByTagName("CheckAdd")
      
Debug.Print "Setup check recordset"
rstChecks.Open sql, cnn, adOpenKeyset, adLockOptimistic
rstPayablesLine.Open sqlPL, cnn, adOpenKeyset, adLockOptimistic
    For Each xmlNodeItem In xmlNodeList


    Debug.Print "rstChecks.add"
    
                rstChecks.AddNew
                rstChecks![Vendor Type] = 1
                
                'rstChecks![Other Number]
                rstChecks![Purchase Order] = 0
                'rstChecks![Purchase Order Number]
                rstChecks![Expense Type] = "ImportedPayable"
                rstChecks![Account ID] = 136
                'rstChecks![Invoice Number]
                
                rstChecks![Amount] = Amount 'passed value, may have to come in an update since we dont know this yet.
                rstChecks![CurrencyCode] = "USD"
                rstChecks![ExchangeRate] = 1
                rstChecks![Due Date] = TxnDate ' passed value
                rstChecks![Terms] = "Check"
                rstChecks![Discount Taken] = 0
                'rstChecks![Purpose of Expense]
                rstChecks![Tax Deductible] = -1
                
                rstChecks![Paid] = 0
                rstChecks![Posted] = 0
                'rstChecks![Posting GL ID]
                rstChecks![Batch Post] = 0
                rstChecks![1099] = 0
                rstChecks![Invoice Received] = -1
                rstChecks![Items Received] = -1
                rstChecks![fldAddedBy] = "currentlogin"
                rstChecks![fldAddedDate] = Now()
                rstChecks![fldnCOGs] = -1
                
                ' Save record so we can get a PayablesID for PayablesLineItems
                rstChecks.Update
                PayableID = rstChecks![bill id]

         Debug.Print "xmlNodeItem.Text: " & xmlNodeItem.Text
         Debug.Print "xmlNodeItem.nodeName: " & xmlNodeItem.nodeName
          Debug.Print "xmlNodeItem.baseName: " & xmlNodeItem.baseName
               Debug.Print " xmlNodeItem.hasChildNodes: " & xmlNodeItem.hasChildNodes
               Debug.Print " xmlNodeItem.nodeTypedValue: " & xmlNodeItem.nodeTypedValue
               Debug.Print " xmlNodeItem.nodeType: " & xmlNodeItem.nodeType
               Debug.Print " xmlNodeItem.nodeTypeString: " & xmlNodeItem.nodeTypeString
               Debug.Print " xmlNodeItem.nodeValu: " & xmlNodeItem.nodeValue
               Debug.Print " xmlNodeItem.parsed: " & xmlNodeItem.parsed
               Debug.Print " xmlNodeItem.prefix: " & xmlNodeItem.prefix
               Debug.Print " xmlNodeItem.specified: " & xmlNodeItem.specified
               
               For Each xmlNodeField In xmlNodeItem.childNodes
                'Debug.Print "  CHILD NODE xmlNodefield.Text:  " & xmlNodeField.Text
                'Debug.Print "  CHILD NODE xmlNodefield.nodeName:  " & xmlNodeField.nodeName
                'Debug.Print "  CHILD NODE xmlNodefield.hasChildNodes:  " & xmlNodeField.hasChildNodes
           

                   If xmlNodeField.nodeName = "PaymentID" Then
                   Debug.Print "  rstChecks!PaymentID" & xmlNodeField.Text
                   Else
                   End If
                   
                   If xmlNodeField.nodeName = "PayeeEntityRef" Then
                        Debug.Print "  rstChecks!PayeeEntityRef" & xmlNodeField.Text
                        rstChecks![Supplier Number] = GetSupplierID(xmlNodeField.Text) ' passed value
                   Else
                   End If
                   
                    If xmlNodeField.nodeName = "TxnDate" Then
                        Debug.Print "  rstChecks!TxnDate" & xmlNodeField.Text
                        rstChecks![Invoice Date] = xmlNodeField.Text ' passed value
                   Else
                   End If
                   
                   If xmlNodeField.nodeName = "Memo" Then
                        Debug.Print "  rstChecks!Memo" & xmlNodeField.Text
                        rstChecks![Comments] = xmlNodeField.Text
                   Else
                   End If
                   
                   
                    If xmlNodeField.nodeName = "ExpenseLineAdd" Then
                       
                        Debug.Print "<ExpenseLineAdd>" & xmlNodeField.Text; ""
                        Debug.Print "    rstCheckLineItems.add"
                        Debug.Print "    PayableID: " & PayableID
                        
                        rstPayablesLine.AddNew
                        rstPayablesLine![bill id] = PayableID
                        'rstPayablesLine![Line Number] ' Autonumber
                        
                        
                        rstPayablesLine![CurrencyCode] = "USD"
                        rstPayablesLine![ExchangeRate] = 1
                        
                    For Each xmlNodeSubItem In xmlNodeField.childNodes
                    
                                
                         
                                If xmlNodeSubItem.nodeName = "AccountRef" Then
                                    Debug.Print "  Add Line AccountRef" & xmlNodeSubItem.Text
                                    rstPayablesLine![Account ID] = GetGLID(xmlNodeSubItem.Text)
                                Else
                                End If
                         
                                If xmlNodeSubItem.nodeName = "Amount" Then
                                    Debug.Print "  Add Line Amount" & xmlNodeSubItem.Text
                                    rstPayablesLine![Amount] = xmlNodeSubItem.Text
                                Else
                                End If
                                
                                If xmlNodeSubItem.nodeName = "Memo" Then
                                    Debug.Print "  Add Line Memo NOT USED" & xmlNodeSubItem.Text
                                Else
                                End If
                                
                                                        
                        Next
                                Debug.Print "    rstCheckLineItems.Update"
                                rstPayablesLine.Update
                   
                                Else
                   End If
                   
                
            Next
           Debug.Print "rstChecks.Update"
           rstChecks.Update
        Next
    
    

    
   
End Function

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now