none
How to find whether attributes with specific values exist in the XML document ?

    Question

  • Hi,

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

    Option Explicit
    Dim sheet As Worksheet
    Dim rowCount1, rowCount2, rowCount3, i As Integer
    Dim xNode As MSXML2.IXMLDOMNode
    Dim xDoc As MSXML2.DOMDocument
    Dim Nodes As MSXML2.IXMLDOMNodeList
    
    Public Sub LoadDocument()
    rowCount1 = 0
    rowCount2 = 0
    rowCount3 = 0
    Set xDoc = New MSXML2.DOMDocument
    xDoc.validateOnParse = False
    If xDoc.load("D:\Feedroutes.xml") Then
     Set Nodes = xDoc.SelectNodes("//Property[@name='Value']")
       ' The document loaded successfully.
     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:

    <Tags>

       <Tag name="Feeders" path="" type="Folder"/>

       <Tag name="R1" path="Feeders" type="Folder"/>

       <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>

    </Tags>


    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




    • Edited by jjag Wednesday, August 28, 2013 10:35 AM
    Wednesday, August 14, 2013 1:42 PM

All replies