CODEWITHSUNDEEP
xmlexcelvba
Jaggan_j
Jaggan_j

Reputation: 518

How to find whether attributes with specific values exist in the XML child elements?

I am trying to find out whether an XML Attribute with a specific value exists or not in each child element of the xml document root. My code is as follows:

Option Explicit
Dim sheet As Worksheet
Dim rowCount1, i As Integer
Dim xNode As MSXML2.IXMLDOMNode
Dim xDoc As MSXML2.DOMDocument
Dim Nodes As MSXML2.IXMLDOMNodeList

Public Sub LoadDocument()
rowCount1 = 0
Set xDoc = New MSXML2.DOMDocument
xDoc.validateOnParse = False
 If xDoc.load("D:\Feedroutes.xml") Then ' The document loaded successfully.
  Set Nodes = xDoc.SelectNodes("//Property[@name='Value']")
  AttributesToColumns
  Else
 ' The document failed to load.
 End If
End Sub

Public Sub AttributesToColumns()
 Set sheet = ActiveSheet

 For Each xNode In Nodes
  If Not Nodes Is Nothing Then
   rowCount1 = rowCount1 + 1
   sheet.Cells(rowCount1, 3).Value = xNode.Text
  'I want to increment the rowCount even if the attribute with the
  'specific value doesn't exist
  ElseIf Nodes Is Nothing Then
  rowCount1 = rowCount1 + 1

  End If
 Next xNode
End Sub

At present it only writes the values of the attributes in successive rows, whereas the desired output is, write only in rows where the attribute with the specific value is present. My XML looks like this:

<Tag name="PosLim" path="Feeders/R1" type="OPC">
  <Property name="Value">0.0</Property>
  <Property name="DataType">4</Property>
  <Property name="OPCServer">Ignition OPC-UA Server</Property>
  <Property name="OPCItemPath">[Siemens]DB141,I74</Property>
  <Property name="ScaleMode">1</Property>
  <Property name="RawHigh">1000.0</Property>
  <Property name="FormatString">#,##0.00</Property>
  <Property name="EngUnit">Kg</Property>
</Tag>
<Tag name="JogSettle" path="Feeders/R1" type="OPC">
  <Property name="DataType">1</Property>
  <Property name="OPCServer">Ignition OPC-UA Server</Property>
  <Property name="OPCItemPath">[Siemens]DB141,I96</Property>
  <Property name="RawHigh">1000.0</Property>
  <Property name="FormatString">#,##0</Property>
  <Property name="EngUnit">S</Property>
</Tag>
<Tag name="Positive Tol" path="Feeders/R1" type="OPC">
  <Property name="Value">0.0</Property>
  <Property name="DataType">4</Property>
  <Property name="OPCServer">Ignition OPC-UA Server</Property>
  <Property name="OPCItemPath">[Siemens]DB141,I78</Property>
  <Property name="ScaleMode">1</Property>
  <Property name="RawHigh">1000.0</Property>
  <Property name="FormatString">#,##0.00</Property>
  <Property name="EngUnit">Kg</Property>
</Tag>

My purpose is to write the values of attribute "name" where name = "Value" and all the other attributes as well in corresponding rows, not successive rows. So from the xml file above, my excel output should be:

   Row No.   Value        ScaleMode 
   Row1        0             1 
   Row2 
   Row3        0             1 etc

Upvotes: 2

Views: 3043

Answers (1)

Jaycal
Jaycal

Reputation: 2087

Your Nodes value only has the Property nodes that have a name=Value, and from that, you can't determine which nodes are being skipped. What you need to do is select all of the Tag nodes, and then check if that Tag has a property with the name=Value.

First add an additional line to hold your Property tags

Dim propertyNode As MSXML2.IXMLDOMNode

Then, in your LoadDocument Sub, select all the tag nodes (instead of selecting the nodes with name=value)

Set Nodes = xDoc.SelectNodes("//Tag")

Now that you have all the tags, you can skip over any tag that doesn't have a property with the name=Value. Replace the code in your For statement in AttributesToColumns Sub with the following.

Set propertyNode = xNode.SelectSingleNode("Property[@name='Value']")
If Not propertyNode Is Nothing Then sheet.Cells(rowCount1, 3).Value = propertyNode.Text
rowCount1 = rowCount1 + 1

Upvotes: 3

Related Questions