Removing Node/Attribute using VBA

Question

I've tried the searches of this site and a google search and can't seem to find an answer to my problem although the searches I did conduct led me to the VBA code you see below. My problem is that I have an XML tree and I need to remove the 8 lines associated with "MyBank1","broccoli" etc and "MyBank2", "broccoli" etc. The files I need to edit could have many instances of "foo" in the file where the "MyBank1" and "MyBank2" lines need to be removed (thus the loop) but leave the rest of the bank information (for example leave the lines associated with "Citi"). The code runs but the resulting file doesn't remove the lines. I have a feeling I'm not understanding XML language, the interaction of VBA with the xml file or more likely, both. Any help is appreciated!

my xml file:

<?xml version="1.0" encoding="utf-8"?>
<IPSGDatas.....xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header>
        <language_id>120</language_id>
    </header>
    <datas>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank1">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank2">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank1">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="MyBank2">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
    </datas>
</IPSGDatas>

Now my VBA Code:

Option Explicit
Public Sub EditDocument()

'declare objects and variables
Dim xDoc As MSXML2.DOMDocument60
Dim xNode As IXMLDOMElement
Dim foo As IXMLDOMNodeList
Dim i As Integer

'initialize object
Set xDoc = New MSXML2.DOMDocument60
xDoc.validateOnParse = False
'load document
xDoc.Load ("C:\Users\Danny\Desktop\xml\TestDoc.xml")

'initialize and select set of nodes
Set foo = xDoc.SelectNodes("/datas/foo")

    'loop to select specific attribute/node and delete it
    For i = 0 To foo.Length - 1
        Set xNode = xDoc.SelectSingleNode("/bar/banks/bank[@name='MyBank1']")
        xNode.Attributes.removeNamedItem "MyBank1"
        Set xNode = xDoc.SelectSingleNode("/bar/banks/bank[@name='MyBank2']")
        xNode.Attributes.removeNamedItem "MyBank2"
    Next i

'save new document
xDoc.Save ("C:\Users\Danny\Desktop\xml\NewFile.xml")
'clear document from memory
Set xDoc = Nothing

End Sub

Answer 1

Could be something like this, HTH.

Set xDoc = New MSXML2.DOMDocument60
xDoc.Load ("C:\Temp\StackOverflow\source.XML")

Dim myBank12 As IXMLDOMNodeList
Set myBank12 = xDoc.SelectNodes("//bank[@name='MyBank1']|//bank[@name='MyBank2']")

Dim xNode As IXMLDOMElement
For Each xNode In myBank12
    xNode.ParentNode.RemoveChild xNode
Next

xDoc.Save "C:\Temp\StackOverflow\result.XML"

Note:

//bank selects all bank elements no matter where they are in the document. And by using the | operator in an xpath expression you can select several paths.

Result:

<?xml version="1.0" encoding="utf-8"?>
<IPSGDatas xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header>
        <language_id>120</language_id>
    </header>
    <datas>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
        <foo>
            <signature/>
            <bar>
                <banks>
                    <marker>
                        <broccoli order="1">X</broccoli>
                        <broccoli order="2">X</broccoli>
                    </marker>
                    <bank name="Citi">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="Keybank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NBT">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                    <bank name="NationalBank">
                        <broccoli order="1">A</broccoli>
                        <broccoli order="2">B</broccoli>
                    </bank>
                </banks>
                <profile_id>MyName1</profile_id>
            </bar>
            <action_id>New</action_id>
            <index_id>1</index_id>
            <agency/>
            <agency_reference/>
            <accreditation_id>U</accreditation_id>
        </foo>
    </datas>
</IPSGDatas>

Answer 2

<signature/> is an empty element with no content, but your indenting makes it look like it's part of the path to <bank>: it is not so leave it out of the xPath.

Eg:

Public Sub EditDocument()


    Dim xDoc As MSXML2.DOMDocument60

    Set xDoc = New MSXML2.DOMDocument60
    xDoc.validateOnParse = False
    xDoc.Load "C:\_Stuff\test\test.xml" 

    DeleteNodes xDoc, "IPSGDatas/datas/foo/bar/banks/bank[@name='MyBank1']"
    DeleteNodes xDoc, "IPSGDatas/datas/foo/bar/banks/bank[@name='MyBank2']"

    xDoc.Save "C:\_Stuff\test\test_updt.xml"  ''save new document
    Set xDoc = Nothing

End Sub

Sub DeleteNodes(xDoc As MSXML2.DOMDocument60, xPath As String)
    Dim foo As IXMLDOMNodeList, el  As IXMLDOMElement
    Set foo = xDoc.SelectNodes(xPath)
    Debug.Print foo.Length & " nodes for " & xPath
    For Each el In foo
        el.ParentNode.RemoveChild el
    Next el
End Sub