VBA Excel: XML, Get certain Node

Question

I have a Problem with selecting certain nodes in a xml. The XML looks like this:

<?xml version="1.0"?>
<GetConfigurationItems Error="False">
    <ConfigurationItem ID="14" Deleted="0">
        <AttachmentTypes DropDownType="14" Filter="%" Deleted="0">
            <AttachmentType ShortDesc="BOA_FIT" VersionNo="2" ID="1D8651D1-99E2-4D77-9BFF-1A667AA9398D">FIT</AttachmentType>
            <AttachmentType ShortDesc="BOA_LIMS" VersionNo="3" ID="F543938A-693F-457A-97AA-010065D0BA4E">Lims</AttachmentType>
            <AttachmentType ShortDesc="BOA_MICRO_PIC" VersionNo="1" ID="CC3FB18D-1E3F-400A-AD52-971A78A5517D">Microscope picture</AttachmentType>
        </AttachmentTypes>
    </ConfigurationItem>
</GetConfigurationItems>

Now i want to save the ID Attribute, BUT from a certain value, for the beginning lets just take FIT. I tried so many variations, i really dont know what im doing wrong.. :( With this Code i get XML from the Webservice:

Webservice = "http://xxx.xxx.xxx/mm/rm/webservice/RMWS_ConfigurationRead.asmx?wsdl"

    functionName = "GetConfigurationItems"
    portName = "RMWS_ConfigurationReadSoap"
    Set DMIService = New DMIService
    Set oXML = CreateObject("msxml2.DOMDocument.6.0")
    oXML.LoadXML DMIService.execute(Webservice, functionName, portName, "<![CDATA[<GetConfigurationItems><ConfigurationItem ID=""" & ID & """ Deleted=""0""/></GetConfigurationItems>]]>")

So here are some of the try's of the connection string(the different trys are bsp1):

    //GetConfigurationItems/ConfigurationItem[@ID="14"]/AttachmentTypes/AttachmentType[text="FIT"]/@ID
    //GetConfigurationItems/ConfigurationItem[@ID="14"]/AttachmentTypes[AttachmentType="FIT"]/@ID
    //GetConfigurationItems/ConfigurationItem[@ID="14"]/AttachmentTypes/[AttachmentType="FIT"]/@ID
    //GetConfigurationItems/ConfigurationItem[@ID="14"]/AttachmentTypes[AttachmentType[@Name="FIT"]/@ID
    //GetConfigurationItems/ConfigurationItem[@ID="14"]/AttachmentTypes[AttachmentType="FIT"]/@ID

ID = oXML.SelectSingleNode(bsp1).Text

Im quite sure thats its just a small failure, but im now trying for too long.. So would be nice if someone can help me out..

Best regards luca

QHarr · Accepted Answer · 2018-09-10 10:33:11Z

3

This seems to work. I am reading your XML sample in from a file.

Option Explicit
Public Sub GetNode()
    Dim xmlDoc      As MSXML2.DOMDocument60
    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.async = False
    xmlDoc.validateOnParse = True

    If Not xmlDoc.Load("C:\Users\User\Desktop\Testing.xml") Then
        MsgBox "Problem"
        Exit Sub
    End If
    Debug.Print xmlDoc.SelectSingleNode("//AttachmentType[text()='FIT']").Attributes.getNamedItem("ID").Text
End Sub

Or

Debug.Print xmlDoc.SelectSingleNode("//*[text()='FIT']").Attributes.getNamedItem("ID").Text

answered Sep 10, 2018 at 10:33

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

1 Comment

LucaS Over a year ago

Thanks a lot. This was exactly what i was looking for, a simple, one Line command to get this damn id out of the xml. Thanks a lot for your help, appreciate it alot! also interessting to know that i dont have to write to whole connectionstring, makes it more readable! have a wonderfull my friend!

Vityata · Accepted Answer · 2018-09-10 10:37:21Z

This is what I have managed to build:

Option Explicit
Sub TestMe()

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

    xmlObj.async = False
    xmlObj.validateOnParse = False
    xmlObj.Load (ThisWorkbook.Path & "\someXML.xml")

    Dim nodesThatMatter As Object
    Dim node            As Object
    Set nodesThatMatter = xmlObj.SelectNodes("//GetConfigurationItems")

    Dim level1 As Object
    Dim level2 As Object
    Dim level3 As Object
    Dim level4 As Object

    For Each level1 In nodesThatMatter
        For Each level2 In level1.ChildNodes
            For Each level3 In level2.ChildNodes
                For Each level4 In level3.ChildNodes
                    With level4
                        If .Attributes(0).Value Like "*FIT*" Then
                            Debug.Print "OK " & .Attributes(0).Value & .Attributes(2).Value
                        Else
                            Debug.Print "IGNORE " & .Attributes(0).Value
                        End If
                        Debug.Print .Text & vbCrLf
                    End With
                Next level4
            Next level3
        Next level2
    Next level1

End Sub

With this in the immediate window:

OK BOA_FIT1D8651D1-99E2-4D77-9BFF-1A667AA9398D
FIT

IGNORE BOA_LIMS
Lims

IGNORE BOA_MICRO_PIC
Microscope picture

The idea is to use the Watches window as much as you can and thus use the attributes. I have started from the upper level - //GetConfigurationItems and went downstream, looping through each one.

Collectives™ on Stack Overflow

VBA Excel: XML, Get certain Node

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related