XML looping through for next VBA 1

[Chance1234]

Public Sub psub_read_convert_XML()

Dim xml_Doc As New DOMDocument
Dim xml_list As IXMLDOMNodeList
Dim xml_itm As IXMLDOMElement
Dim xml_item1 As IXMLDOMElement
Dim xml_item2 As IXMLDOMElement
Dim xml_item3 As IXMLDOMElement
Dim xml_item4 As IXMLDOMElement
Dim xml_item5 As IXMLDOMElement
Dim str1, str2, str3, str4, str5, str6 As String
    
Dim rst_feed As DAO.Recordset

'On Error Resume Next
Set db_xml = CurrentDb()
Set rst_feed = db_xml.OpenRecordset("tbl_feed")


    

xml_Doc.Load ("H:\mydata\test.xml")
Set xml_list = xml_Doc.documentElement.childNodes
   
For Each xml_itm In xml_list.nextNode.childNodes
    For Each xml_item1 In xml_itm.childNodes
        If xml_item1.nodeName = "FundCode" Then
            str1 = xml_item1.Text
        End If
        
        If xml_item1.nodeName = "Dividends" Then
            For Each xml_item2 In xml_item1.childNodes
                  For Each xml_item3 In xml_item2.childNodes
                        If xml_item3.nodeName = "XdDate" Then
                            str2 = xml_item3.Text
                        End If
                        
                        If xml_item3.nodeName = "PaymentDate" Then
                            str3 = xml_item3.Text
                        End If
                        
                        If xml_item3.nodeName = "Payment" Then
                           For Each xml_item4 In xml_item3.childNodes
                                If xml_item4.nodeName = "Float" Then
                                    str4 = xml_item4.Text
                                End If
                           Next
                        End If
                        
                        If xml_item3.nodeName = "TaxOp" Then
                            str5 = xml_item3.Text
                        End If
                        
                  Next
                  
                  With rst_feed
                        .AddNew
                        !feed_Lipper_id = str1
                        !feed_xd_date = str2
                        !feed_Payment_date = str3
                        !feed_payment = str4
                        !feed_tax_code = str5
                        .Update
                   End With
                   
            Next
        End If
        
    Next
Next
   
    

End Sub

I have the above code which works perfectly for what I was doing but...

Here is a snippet of the xml im working with

<?xml version="1.0" encoding="UTF-8" ?> 
- <GFDResponse>
- <ResponseFundDetails>
- <Asset>
  <FundCode>600XXX3</FundCode> 
  <XRefSchemeCode>FTMEXID</XRefSchemeCode> 
  <XRefCode>BGXXXXA</XRefCode> 
  <FundName>BaXXXXXXXc A</FundName> 
  <FundShortName>BaiXXXXXXX A</FundShortName> 
  <NameInHouse>BailliXXXXXXXXXXXX A</NameInHouse> 
  <FundStatus>Active</FundStatus> 
  <Frequency>Daily</Frequency> 
  <QualifiedHistoryDate>2000-XXXXX</QualifiedHistoryDate> 
  <LastReviewedDate>2003XXXXX</LastReviewedDate> 
- <SinglePriceCharge>
  <Float>X.XX</Float> 
  </SinglePriceCharge>
  <IncomeDistribution>Retained</IncomeDistribution> 
  <IncomeIsInterest>No</IncomeIsInterest> 
  <DefaultTaxOp>UK Net</DefaultTaxOp> 
  <DivsPerYearCount>1</DivsPerYearCount> 
- <FundXRefs>
- <FundXRef>
  <XRefSchemeCode>ADMIN</XRefSchemeCode> 
  <XRefSchemeName>Administrator Code</XRefSchemeName> 
  <XRefCode>27XXX3</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>AUTIF</XRefSchemeCode> 
  <XRefSchemeName>IMA/AUTIF Code</XRefSchemeName> 
  <XRefCode>2XXX6</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>FINEXCITY</XRefSchemeCode> 
  <XRefSchemeName>FinEx CitiCode</XRefSchemeName> 
  <XRefCode>BXXX82</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>FTMEXID</XRefSchemeCode> 
  <XRefSchemeName>FT MexId</XRefSchemeName> 
  <XRefCode>BXXXAA</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>GFAEFID</XRefSchemeCode> 
  <XRefSchemeName>GFA/EFID Code</XRefSchemeName> 
  <XRefCode>2XXX84</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>ISIN</XRefSchemeCode> 
  <XRefSchemeName>ISIN Code</XRefSchemeName> 
  <XRefCode>GBXXXX1740</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>ISINCURR</XRefSchemeCode> 
  <XRefSchemeName>ISIN Currency Class</XRefSchemeName> 
  <XRefCode>GB000XXXX40.GBX</XRefCode> 
  </FundXRef>
- <FundXRef>
  <XRefSchemeCode>SEDOL</XRefSchemeCode> 
  <XRefSchemeName>Sedol Code</XRefSchemeName> 
  <XRefCode>6XXX74</XRefCode> 
  </FundXRef>
  </FundXRefs>
- <Dividends>
- <Dividend>
  <Date>2001-04-30</Date> 
  <CurrencyCode>GBX</CurrencyCode> 
  <XdPriceBuying /> 
- <XdPriceCurrent>
  <Float>155.20</Float> 
  </XdPriceCurrent>
  <PaymentPriceBuying /> 
- <PaymentPriceCurrent>
  <Float>146.40</Float> 
  </PaymentPriceCurrent>
  <XdDate>2001-04-30</XdDate> 
  <PaymentDate>2001-07-31</PaymentDate> 
  <AccountingDate>2001-04-30</AccountingDate> 
- <Payment>
  <Float>0.00</Float> 
  </Payment>
- <PaymentExUK>
  <Float>0.00</Float> 
  </PaymentExUK>
  <IsSpecialPayment>No</IsSpecialPayment> 
  <TaxOp>UK Net</TaxOp> 
  <IncomeOp>Reinvested</IncomeOp> 
  </Dividend>
- <Dividend>
  <Date>2002-04-30</Date> 
  <CurrencyCode>GBX</CurrencyCode> 
- <XdPriceBuying>
  <Float>139.50</Float> 
  </XdPriceBuying>
- <XdPriceCurrent>
  <Float>139.50</Float> 
  </XdPriceCurrent>
- <PaymentPriceBuying>
  <Float>115.50</Float> 
  </PaymentPriceBuying>
- <PaymentPriceCurrent>
  <Float>115.50</Float> 
  </PaymentPriceCurrent>
  <XdDate>2002-04-30</XdDate> 
  <PaymentDate>2002-07-31</PaymentDate> 
- <Payment>
  <Float>0.00</Float> 
  </Payment>
  <PaymentExUK /> 
  <IsSpecialPayment>No</IsSpecialPayment> 
  <TaxOp>UK Net</TaxOp> 
  <IncomeOp>Reinvested</IncomeOp> 
  </Dividend>
- <Dividend>
  <Date>2003-05-02</Date> 
  <CurrencyCode>GBX</CurrencyCode> 
- <XdPriceBuying>
  <Float>112.60</Float> 
  </XdPriceBuying>
- <XdPriceCurrent>
  <Float>112.60</Float> 
  </XdPriceCurrent>
- <PaymentPriceBuying>
  <Float>119.90</Float> 
  </PaymentPriceBuying>
- <PaymentPriceCurrent>
  <Float>119.90</Float> 
  </PaymentPriceCurrent>
  <XdDate>2003-05-02</XdDate> 
  <PaymentDate>2003-07-31</PaymentDate> 
  <AccountingDate>2003-04-30</AccountingDate> 
- <Payment>
  <Float>0.20</Float> 
  </Payment>
  <PaymentExUK /> 
  <IsSpecialPayment>No</IsSpecialPayment> 
  <TaxOp>UK Net</TaxOp> 
  <IncomeOp>Reinvested</IncomeOp> 
  </Dividend>
  </Dividends>
  </Asset>
  </ResponseFundDetails>
  </GFDResponse>

Now what i was doing was saving the dividend information to an access table which was working grand, but I've jsut realised i also need to store next to the dividend information the Sedol number which is under another node.

anyone see an easy way out of this ?

Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[jel]

I found it quit difficult to read all those loops, you can make it a lot easier by using x-path expressions.
Doing so, you'll easily find any node you want.

Dim strFundCode As String
Dim strSedolCode As String
Dim strXdDate As String
Dim strPayDate As String
Dim strPayment As String
Dim strTaxOp As String

Dim ndeAsset As IXMLDOMNode
Dim ndeDividend As IXMLDOMNode

For Each ndeAsset In xml_Doc.selectNodes("//Asset")

    strFundCode = ndeAsset.selectSingleNode("FundCode").Text
    strSedolCode = ndeAsset.selectSingleNode _
          ("FundXRefs/FundXRef[XRefSchemeCode='SEDOL']/XRefCode").Text

    For Each ndeDividend In ndeAsset.selectNodes("Dividends/Dividend")

        strXdDate = ndeDividend.selectSingleNode("XdDate").Text
        strPayDate = ndeDividend.selectSingleNode("PaymentDate").Text
        strPayment = ndeDividend.selectSingleNode("Payment/Float").Text
        strTaxOp = ndeDividend.selectSingleNode("TaxOp").Text

        Debug.Print strFundCode, strSedolCode, strXdDate, _
                      strPayDate, strPayment, strTaxOp
    Next
Next

By the way, you'll get an error if a node doesn't exist.
You might prevent that this way:
set Node = ndeDividend.selectSingleNode("TaxOp")
if Node is nothing then
strTaxOp = ""
else
strTaxOp= Node.Text
end if

But I noticed that in your loops, if a node doesn't exist, you use the value of the last node with the same X-path - so you must be quit confident that they do exist. ;-)

 

[Chance1234]

cheers Jel thats a great help,

yeah im quite confident they do exist as this is a reuters feed ;-)



Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM