Find and replace an XML node

Question

I have an XML file defining the processes and some other parameters, which will be loaded by a CAN controller at start up. I will use this as a template for differrent controllers, so I need to manipulate the items in some sections of the XML. In particular I will be defining different Subcomponents and modifying some of the existing ones. And I have to use Excel VBA to handle all this stuff: eventually there will be tenths if not hundreds of controller with different definitions.

I've managed to get around the process of cloning and appending new nodes, but it looks like my brain is refusing to grasp the concepts behind the XML parsing, editing etc.

My problem at hand now is how to find and replace (or not if one or more attributes already satisfy some conditions: path on disk mostly) a child node of the Subcomponents tree. Here the template:



   This is a template for creating a controller applications.
  12

In particular: say that I want to delete:

And also to change:

to:

For removing I tried:

Set appXML = New MSXML2.DOMDocument
With appXML
    If Not .LoadXML("ControllerApp.xml") Then Exit Sub
    Set xmlSubTree = .SelectSingleNode("/Application/Subcomponents/Subcomponent").ParentNode
    dumString =  "//Subcomponent[@Name='SerialStringCom1']"
    Set oldNode = xmlSubTree.SelectSingleNode(dumString)
    ' checking oldNode here gives all the expected attributes values (text)
    ' but it fails on the next statement: Object does not support this property or method:
    oldNode.ParentNode.RemoveChild(oldNode)
End With

Yitzhak Khabinsky · Accepted Answer

It is very easy to achieve by using XSLT. You just need to call XSLT from VBA.

XSLT will process your input XML and generate a new XML file:

XML + XSLT => new XML

XSLT

VBA

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

    Dim source As New MSXML2.DOMDocument60
    Dim stylesheet As New MSXML2.DOMDocument60
    Dim result As New MSXML2.DOMDocument60

    ' Load data.
    source.async = False
    source.Load sourceFile

    ' Load style sheet.
    stylesheet.async = False
    stylesheet.Load stylesheetFile

    If (source.parseError.ErrorCode <> 0) Then
       MsgBox ("Error loading source document: " & source.parseError.reason)
    Else
        If (stylesheet.parseError.ErrorCode <> 0) Then
            MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
        Else
            ' Do the transform.
            source.transformNodeToObject stylesheet, result
            result.Save resultFile
        End If
    End If

End Sub

Find and replace an XML node

Answers (1)

Related Questions