Parsing XML - Don't understand where Nodelist.Item is being found

[mchac]

I'm working through borrowed code to understand how it parses XML and how bits of data are being found. I've very new to XML and parsing so apologies in advance if this is a basic question.

When I look at oNotelist2 in the Watch window I can see it has Item(1), Item(2), Item(3) and Item(4) but I don't understand:
1: Where is Item(0) (which I think is where it should be seeking @contextRef)?
2: Where is the value of strContextID = 'I2013Q4' being found?

Sub tester2()


    Dim oInstance As MSXML2.DOMDocument60
    Dim m_strUSGAAP_TaxonomyVersion As String
    Dim m_strInvest_TaxonomyVersion As String
    Dim m_strDEI_TaxonomyVersion As String


    Dim oNodelist2 As MSXML2.IXMLDOMNodeList
    Dim strContextID As String
    Dim i As Long
    
    Set oInstance = New MSXML2.DOMDocument60
    oInstance.async = False
    oInstance.validateOnParse = False
    oInstance.Load "http://www.sec.gov/Archives/edgar/data/1143908/000114390813000035/ck0001143908-20130831.xml"


    m_strUSGAAP_TaxonomyVersion = "http://fasb.org/us-gaap/2012-01-31"
    m_strInvest_TaxonomyVersion = "http://xbrl.sec.gov/invest/2012-01-31"
    m_strDEI_TaxonomyVersion = "http://xbrl.sec.gov/dei/2012-01-31"


    oInstance.setProperty "SelectionNamespaces", "xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xbrli='http://www.xbrl.org/2003/instance' xmlns='http://www.xbrl.org/2003/instance' xmlns:xbrldi='http://xbrl.org/2006/xbrldi' xmlns:us-gaap='" & m_strUSGAAP_TaxonomyVersion & "' xmlns:dei='" & m_strDEI_TaxonomyVersion & "' xmlns:currency='http://xbrl.sec.gov/currency/2012-01-31' xmlns:invest='" & m_strInvest_TaxonomyVersion & "'"


    
    Set oNodelist2 = oInstance.SelectNodes("//us-gaap:Assets | //us-gaap:AssetsCurrent | //us-gaap:LiabilitiesAndStockholdersEquity")


    i = 0
    strContextID = oNodelist2.Item(i).SelectSingleNode("@contextRef").Text


    Debug.Print strContextID


End Sub

Thanks in advance for your help.

 

[deanst]

Hi,

The code is parsing the XML file using XPath search expressions.

In the xml file http://www.sec.gov/Archives/edgar/data/1143908/000114390813000035/ck0001143908-20130831.xml you can see there are six items which match the search phrase in the SelectNodes function. This function is basically looking for any nodes with either of those 3 expressions.

The selectNodes function return 6 items (you can see by Debug.Print(oNodelist2.length).

In VBA, item 1 of a list if referred to as index 0, hence using oNodelist2.item(0). The selectsinglenode function is then getting the value of the attribute contextRef. Looking inside the xml file you can see the line

<font color="#000000" face="courier new">

<us-gaap:Assets contextRef="I2013Q4" decimals="-3" id="Fact-6E24E61A0F9FEC72FBB7CFCE98780FB5" unitRef="usd">16867049000</us-gaap:Assets>

which is where it is getting I2013Q4 from.

You can see more info on XPath expressions here: http://www.w3schools.com/xpath/xpath_syntax.asp

Dean.

 

[mchac]

Thanks a lot deanst.

I was playing around with this yesterday while MrExcel was being updated and found that this gives the result also:

Debug.Print oInstance.SelectSingleNode("//us-gaap:Assets").Attributes.getNamedItem("contextRef").NodeValue

So, are these two functionally equivalent in returning the value from the first node?

Set oNodelist2 = oInstance.SelectNodes("//us-gaap:Assets")
oNodelist2.Item(0).SelectSingleNode("@contextRef").Text

and

oInstance.SelectSingleNode("//us-gaap:Assets").Attributes.getNamedItem("contextRef").NodeValue

Thanks for your help.

 

[deanst]

Yes your formula's are functionally equivalent.