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

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
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?

Code:
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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">
HTML:
<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.
 
Last edited:
Upvote 0
Thanks a lot deanst.

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

Code:
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?
Code:
Set oNodelist2 = oInstance.SelectNodes("//us-gaap:Assets")
oNodelist2.Item(0).SelectSingleNode("@contextRef").Text
and
Code:
oInstance.SelectSingleNode("//us-gaap:Assets").Attributes.getNamedItem("contextRef").NodeValue

Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top