VBA select XML element val by attribute name

Question

I've been searching SO (and the rest of the Internet) for the answer, but I can't seem to find a solution for selecting an XML node based on an attribute.
This is my XML below this is for placing productcategoryid from a REST service XML

  <lst name="responseHeader">
    <int name="status">0</int>
    <int name="QTime">0</int>
    <lst name="params">
      <str name="q">*:*</str>
      <str name="indent">true</str>
      <str name="wt">xml</str>
    </lst>
  </lst>

  <result name="response" numFound="5429" start="0">
    <doc>
      <int name="idProductCategory">2</int>
      <str name="categoryname">Live Animals</str>
      <int name="categoryLevel">2</int>
      <str name="bestOfferEnabled">false</str>
      <str name="leafCategory">true</str>
      <int name="parentCategoryId">1</int>
      <long name="_version_">1535190804282212352</long>
    </doc>
  </result>

</response>

I need to get the element of idProductCategory, i.e. 2, through VBA code, but I can't make it from below code.

 Sub getProductCategory(prodCatName As String)
    Dim result1 As String
    Dim result As String
    Dim myURL As String
    Dim winHttpReq As Object

    Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

    myURL = "http://localhost:8080/solr/category/select?q=" & prodCatName & "&wt=json"

    MsgBox myURL

    winHttpReq.Open "GET", myURL, False
    winHttpReq.Send

    MsgBox winHttpReq.responseText

    Dim doc_XML As DOMDocument60
    Set doc_XML = New DOMDocument60
    result = winHttpReq.responseText
    doc_XML.Load result

    Set List = doc_XML.documentElement.childNodes
    For Each sub_list In List
        If sub_list.Attributes(0).Text = "response" Then
            For Each Node In sub_list.childNodes(0).childNodes
                If Node.Attributes(0).Text = "idProductCategory" Then
                    result1 = Node.nodeTypedValue
                End If
            Next Node
        End If
    Next sub_list

End Sub

So please help me, I'm struggling on this I need to get element value by attribute name from this above XML and place it in a particular cell in Excel.

i hv read it but how can i get the element val from the idProductCategory ie i needed 2 val from that pls help — user2474367
– user2474367, Commented Sep 8, 2016 at 8:54

m e · Accepted Answer · 2016-09-08 09:49:29Z

1

This code works, it is less elegant than the query you tried to use, but IMO it is easier to understand, as working with xml can be a bit confusing.

Sub prueba2()
Dim doc_XML As DOMDocument60

Set doc_XML = New DOMDocument60

data = winHttpReq.responseText
doc_XML.Load data

Set List = doc_XML.DocumentElement.ChildNodes
For Each sub_list In List
    If sub_list.Attributes(0).Text = "response" Then
        For Each Node In sub_list.ChildNodes(0).ChildNodes
            If Node.Attributes(0).Text = "idProductCategory" Then
                result = Node.nodeTypedValue
            End If
        Next Node
    End If
Next sub_list
End Sub

The xml example used was:

<response>
<lst name="responseHeader">
  <int name="status">0</int>
  <int name="QTime">0</int>
  <lst name="params">
    <str name="q">*:*</str>
    <str name="indent">true</str>
    <str name="wt">xml</str>
  </lst>
</lst>
<result name="response" numFound="5429" start="0">
  <doc>
    <int name="idProductCategory">2</int>
    <str name="categoryname">Live Animals</str>
    <int name="categoryLevel">2</int>
    <str name="bestOfferEnabled">false</str>
    <str name="leafCategory">true</str>
    <int name="parentCategoryId">1</int>
    <long name="_version_">1535190804282212352</long>
  </doc>
</result>
</response>

edited Sep 8, 2016 at 9:49

answered Sep 8, 2016 at 6:32

m e

2914 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user2474367 Over a year ago

thank u ill let u know by trying now thank u so much

user2474367 Over a year ago

its showing object not set on Set List = doc_XML.DocumentElement.ChildNodes(0).ChildNodes

user2474367 Over a year ago

i have added microsoft xml 6.0 in reference

m e Over a year ago

After adding the reference, what error do you get exactly??

user2474367 Over a year ago

still same error sir pls see updated code Object variable or with block variable not set on Set List = doc_XML.DocumentElement.ChildNodes

|

gembird · Accepted Answer · 2016-09-08 10:07:30Z

Using SelectSingleNode the code could look like this. HTH

' Add reference to Microsoft XML v6.0 library

Public Const XML As String = _
    "<response>" & _
    "<lst name='responseHeader'>" & _
      "<int name='status'>0</int>" & _
      "<int name='QTime'>0</int>" & _
      "<lst name='params'>" & _
        "<str name='q'>*:*</str>" & _
        "<str name='indent'>true</str>" & _
        "<str name='wt'>xml</str>" & _
      "</lst>" & _
    "</lst>" & _
    "<result name='response' numFound='5429' start='0'>" & _
      "<doc>" & _
        "<int name='idProductCategory'>2</int>" & _
        "<str name='categoryname'>Live Animals</str>" & _
        "<int name='categoryLevel'>2</int>" & _
        "<str name='bestOfferEnabled'>false</str>" & _
        "<str name='leafCategory'>true</str>" & _
        "<int name='parentCategoryId'>1</int>" & _
        "<long name='_version_'>1535190804282212352</long>" & _
      "</doc>" & _
    "</result>" & _
    "</response>"

Sub test()
    Dim xmlDocument As MSXML2.DOMDocument60
    Set xmlDocument = New DOMDocument60

    If Not xmlDocument.LoadXML(XML) Then
        Err.Raise xmlDocument.parseError.ErrorCode, , xmlDocument.parseError.reason
    End If

    Dim nodeIdProductCategory As IXMLDOMNode
    Set nodeIdProductCategory = xmlDocument.SelectSingleNode("/response/result/doc/int[@name='idProductCategory']")
    If Not nodeIdProductCategory Is Nothing Then
        MsgBox nodeIdProductCategory.text
    Else
        MsgBox "Node witd name 'idProductCategory' was not found."
    End If
End Sub

Collectives™ on Stack Overflow

VBA select XML element val by attribute name

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related