Select a single node XML object using VBA

Question

Hi I'm trying to access the XML response object below

<?xml version='1.0' encoding='UTF-8'?>
  <gfi_message version="1.0">
    <header>
      <transactionId>123</transactionId>
      <timestamp>2018-02-08T15:59:41+08:00</timestamp>
      <processingTime>0.15</processingTime>
    </header>
    <body>
      <response name="action1" function="PRICING" version="1.0">
        <option name="data" ref="price169" />
      </response>
      <data format="NAME_VALUE" name="price169">
        <node name="European Call">
          <field name="Scenario" value="Trading" />
          <field name="Currency" value="USD" status="input" />
          <field name="CtrCcy" value="HKD" status="input" />
          <field name="Strategy" value="Call" status="input" />
          <field name="Model" value="Analytic" />
          <field name="Class" value="European" status="input" />
          <field name="Direction" value="Buy" status="input" />
          <field name="Spot" value="7.81241/7.82871" />
          <field name="Cutoff" value="TOK" />
          <field name="Market" value="OTC" />
          <field name="HorDate" value="15:59 Thu 8 Feb 18" status="input" />
          <field name="ValDate" value="12 Feb 18" />
          <field name="SpotDate" value="12 Feb 18" />
          <field name="Maturity" value="Odd Date" />
          <field name="ExDate" value="8 Feb 18" status="input" />
          <field name="ExDays" value="0" />
          <field name="ExTime" value="14:00 SGT" />
          <field name="DelDate" value="12 Feb 18" />
          <field name="DelDays" value="0" />
          <field name="PremDate" value="Mon 12 Feb 18" />
          <field name="PremType" value="Spot" />
          <field name="Strike" value="7.81241" />
          <field name="CtrStk" value="0.128001474576987" />
          <field name="FwdWealth" value="0\-0.002082079933987" status="input" />
      </node>
    </data>
  </body>
</gfi_message>

Specifically I just need <field name="Spot" value="7.81241/7.82871" />

Currently I'm trying to parse and attempt to print the statements in a debugger but I'm not getting any response as well

Dim objXML As MSXML2.DOMDocument30
Dim nodesThatMatter As MSXML2.IXMLDOMElement
Set nodesThatMatter = objXML.SelectNodes("//gfi_message/body/data/node")

For Each Node In nodesThatMatter
    Debug.Print nodesThatMatter.SelectSingleNode("field").Text
Next

Pankaj Jaju · Accepted Answer · 2018-02-08T11:08:50.847

You can find that particular node by simply using /gfi_message/body/data/node/field[@name='Spot'] xpath

Note - Avoid using // in xpaths as it is slow.

Sample Code

Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
With objXML
    .SetProperty "SelectionLanguage", "XPath"
    .ResolveExternals = True
    .ValidateOnParse = True
    .Async = False
    .Load "C:\Users\pankaj.jaju\Desktop\test2.xml"
End With

Set objNode= objXML.SelectSingleNode("/gfi_message/body/data/node/field[@name='Spot']")
Msgbox objNode.XML

Output

Vityata · Answer 2 · 2018-02-08T10:23:18.943

Here you go with the "Spot":

Option Explicit

Sub TestMe()

    Dim xmlObj As Object
    Set xmlObj = CreateObject("MSXML2.DOMDocument")

    xmlObj.async = False
    xmlObj.validateOnParse = False
    xmlObj.Load ("C:\myaddress\test.xml")

    Dim nodesThatMatter As Object
    Dim node            As Object

    Set nodesThatMatter = xmlObj.SelectNodes("//gfi_message/body/data/node")
    For Each node In nodesThatMatter
        'Debug.Print node.XML
        Dim child   As Variant
        For Each child In node.ChildNodes
            If InStr(1, child.XML, "name=""Spot""") Then
                Debug.Print child.XML
            End If
        Next child
    Next node
End Sub

It is a good idea to use LateBinding, when you are giving code in StackOverflow, or at least to share the library you are using. The code does the following:

loads XMLObject from the PC xmlObj.Load ("C:\myaddress\test.xml")
assigns the nodes that are on the address gfi_message/body/data/node to a variable nodesThatMatter.
starts looping through this variable - in the example there is only one node
in the ChildNodes of this node, it looks for the "name="Spot" string in the .XML property.
it prints what it finds

Or take a look at this VBA-XML convertor (have not tried it, but it looks ok).

score 0 · Answer 3 · answered Feb 08 '18 at 17:39

Another approach will be something like below. It will give you the specific node and it's value.

Sub DemoXML()
    Dim post As Object

    With CreateObject("MSXML2.DOMDocument")
        .async = False: .validateOnParse = False
        .Load (ThisWorkbook.Path & "\some_file.xml")

        Set post = .SelectNodes("//node//field[@name='Spot']")(0)
        MsgBox post.XML
        MsgBox post.getAttribute("value")
    End With
End Sub

Output:

<field name="Spot" value="7.81241/7.82871"/>
7.81241/7.82871

Select a single node XML object using VBA

3 Answers3

Linked