0

I need help with updating a particular node of the XML file. I use the MSXML2.DOMDocument60. This code demonstrates the idea:

Option Explicit

Sub UpdateXML()
    'Load the XML file into oDoc
    Dim oDoc As MSXML2.DOMDocument60
    Set oDoc = New MSXML2.DOMDocument60
    If Not oDoc.Load("C:\1\test.xml") Then
        Debug.Print oDoc.parseError
        Exit Sub
    End If
    
    Dim xPath As String
    'Lets say I want to update this node:
    xPath = "/root/devices/device[@name='DB']/package[@name='DIL8']/technologies"
    
    Dim sNode As IXMLDOMNode
    'I know how to select it
    Set sNode = oDoc.selectSingleNode(xPath)
    
    If Not sNode Is Nothing Then
        Debug.Print sNode.XML
        
        'This function returns the node with new data (function follows)
        Debug.Print getTechnologies.XML
        
        'Here I need to insert the data returned by getTechnologies()
        'into the correct place of the oDoc (specified by xPath)
        
        'this does not work
        oDoc.selectSingleNode(xPath) = getTechnologies
        '??? odoc.replaceChild ??? I'm lost here
         
        'It would be great to see the example how to insert and delete
        'the node <technologies> of the oDoc using xPath... if it is possible of course.
        
    End If
    
    'Save modified data into new file
    oDoc.Save "C:\1\final.xml"
    
    'Final file should now contain "newValue" within the
    '/root/devices/device[@name='DB']/package[@name='DIL8']/technologies
End Sub

Function getTechnologies() As IXMLDOMNode
    'This is just a simplified example to demonstrate the function
    'that returns the IXMLDOMNode object
    'In real, this function pulls data from a database
    
    Dim oNode As MSXML2.DOMDocument60
    Set oNode = New MSXML2.DOMDocument60
    Dim sXml As String
    sXml = "<technologies>" & vbCrLf & _
           "    <property name='prop1' value='newValue'/>" & vbCrLf & _
           "    <property name='prop2' value='newValue'/>" & vbCrLf & _
           "    <property name='prop3' value='newValue'/>" & vbCrLf & _
           "    <property name='prop4' value='newValue'/>" & vbCrLf & _
           "</technologies>"
    If Not oNode.loadXML(sXml) Then
        Debug.Print oNode.parseError
    Else
        Set getTechnologies = oNode.selectSingleNode("/technologies")
    End If
End Function

Here's the file test.xml that I use in my example. It is a simplified version of the real file:

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <library>
        <items>
            <item name="foo"/>
            <item name="bar"/>
            <item name="foo2"/>
            <item name="bar2"/>
        </items>
    </library>
    <devices>
        <device name="DB">
            <package name="DIL4">
                <something>Another tree could be here</something>
                <technologies>
                    <property name="prop1" value="oldValue"/>
                    <property name="prop2" value="oldValue"/>
                    <property name="prop3" value="oldValue"/>
                    <property name="prop4" value="oldValue"/>
                </technologies>
            </package>
            <package name="DIL8">
                <technologies>
                    <property name="prop1" value="oldValue"/>
                    <property name="prop2" value="oldValue"/>
                    <property name="prop3" value="oldValue"/>
                    <property name="prop4" value="oldValue"/>
                </technologies>
                <something>The order is not guaranteed</something>
            </package>
            <package name="DIL16">
                <technologies>
                    <property name="prop1" value="oldValue"/>
                    <property name="prop2" value="oldValue"/>
                    <property name="prop3" value="oldValue"/>
                    <property name="prop4" value="oldValue"/>
                </technologies>
            </package>
        </device>
        <device name="NPN">
            <package name="SOT23">
                <technologies>
                    <property name="prop1" value="oldValue"/>
                    <property name="prop2" value="oldValue"/>
                    <property name="prop3" value="oldValue"/>
                    <property name="prop4" value="oldValue"/>
                </technologies>
            </package>
        </device>
    </devices>
</root>

EDIT: Below is the code of this answer but I don't understand how modifying the xmlRoot can afect the xmlDoc - is it byRef? (see the notes in the code)

Sub XMLTest()
Dim myVar As String, pathToXML As String
Dim xmlDoc As Object, xmlRoot As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    pathToXML = "N:\example.xml" '<--- change the path
    Call xmlDoc.Load(pathToXML)
    Set xmlRoot = xmlDoc.getElementsByTagName("RefTest").Item(0)
    myVar = "foobar" '<--- your value

    'Here the xmlRoot object is updated
    xmlRoot.selectSingleNode("iRef5").Text = myVar
    
    'Here the xmlDoc is saved
    Call xmlDoc.Save(pathToXML)
End Sub

I feel like the answer is right in front of my eyes, but I can't see it

Combinatix
  • 1,186
  • 3
  • 12
  • 24
  • Can't you just directly assign new xml to the target node? You have a reference to it when you SET it in a variable. – QHarr Jan 17 '21 at 21:34
  • Does this answer your question? [Can I update an XML node value using a variable in VBA](https://stackoverflow.com/questions/17042869/can-i-update-an-xml-node-value-using-a-variable-in-vba) – June7 Jan 17 '21 at 22:06
  • Review https://stackoverflow.com/questions/5506548/remove-a-node-from-xml-file-in-ms-project-vba and https://stackoverflow.com/questions/38104423/create-brand-new-xml-node-to-append-as-child-to-other-node – June7 Jan 17 '21 at 22:10
  • @June7 These articles use For Each loop, which is not exactly what I want. I believe it should be possible to insert / edit / delete the node directly using XPATH. I have expanded my question - please see the EDIT – Combinatix Jan 18 '21 at 09:57
  • Review this https://forums.asp.net/t/1602330.aspx?Adding+a+full+node+to+an+existing+XML+document+on+a+by+xpath+specified+location – June7 Jan 18 '21 at 14:09

2 Answers2

1

Simple xml file:

<?xml version="1.0"?>
<!-- This file represents a fragment of a bookstore inventory database -->
<bookstore specialty="novel">
  <book>
    <Title>Beginning XML</Title>
    <Publisher>Wrox</Publisher>
  </book>
  <book>
    <Title>Professional XML</Title>
    <Publisher>Wrox</Publisher>
  </book>
  <book>
    <Title>Programming ADO</Title>
    <author>
      <first-name>Mary</first-name>
      <last-name>Jones</last-name>      
    </author>
    <datePublished>1/1/2000</datePublished>
    <Publisher>Microsoft Press</Publisher>
  </book>
</bookstore>

Following worked for me to edit a single node.

Dim oDoc As MSXML2.DOMDocument60, sNode As MSXML2.IXMLDOMNode
Set oDoc = New MSXML2.DOMDocument60
oDoc.Load "path\filename"
Set sNode = oDoc.SelectSingleNode("//book[3]/Title") 'selection criteria using XPath syntax
sNode.Text = "something"
oDoc.Save("path\filename") 'If same path\filename is used, it will overwrite.
June7
  • 19,874
  • 8
  • 24
  • 34
  • All right but how come that updating sNode is modifying the oDoc? What makes these objects linked? Is it ByReference? Sorry if it's a stupid question – Combinatix Jan 18 '21 at 14:22
  • 1
    oDoc is parent to sNode as defined by: `Set sNode = oDoc. ...`. sNode is a part of oDoc. That's why oDoc must be set and loaded first. That's the best I can explain. – June7 Jan 18 '21 at 14:26
  • By other words: sNode is a reference to the part of the oDoc selected by xPath, thus accessing the sNode properties actually modifies the oDoc. Correct? (I'm not a native English so it takes a while ;) – Combinatix Jan 18 '21 at 14:41
  • That seems to cover it. – June7 Jan 18 '21 at 14:44
0

The whole problem was that I thought that the oDoc and sNode objects were independent of each other. I didn't realize that sNode is an active reference to the oDoc node. Thanks to the June7 answer, I understood how it works and then it only took a moment to find the answer to all 3 questions that I originally had in mind:

Sub UpdateXML()
    'Load the XML file into oDoc
    Dim oDoc As MSXML2.DOMDocument60
    Set oDoc = New MSXML2.DOMDocument60
    If Not oDoc.Load("C:\1\test.xml") Then
        Debug.Print oDoc.parseError
        Exit Sub
    End If

    Dim xPath As String
    'Lets say I want to update this node:
    xPath = "/root/devices/device[@name='DB']/package[@name='DIL8']/technologies"

    Dim sNode As IXMLDOMNode         'Reference to oDoc node
    Set sNode = oDoc.selectSingleNode(xPath)
    If Not sNode Is Nothing Then     'If node is found then
    
        Dim nTech As IXMLDOMNode
        Set nTech = getTechnologies  'Get node from function
    
        'To update selected node
        sNode.parentNode.replaceChild nTech, sNode
        
        'To remove node (Reference to sNode must be set again after replaceChild method)
        Set sNode = oDoc.selectSingleNode(xPath)
        sNode.parentNode.removeChild sNode
        
        'To insert new node into a particular node
        xPath = "/root/devices/device[@name='DB']/package[@name='DIL8']"
        Set sNode = oDoc.selectSingleNode(xPath)
        sNode.appendChild nTech

    End If
    'Save modified data into new file
    oDoc.Save "C:\1\final.xml"
End Sub
Combinatix
  • 1,186
  • 3
  • 12
  • 24