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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related