Search code examples
vbams-accessmsxml2

MSXML2 - How to search specific nodes and replace its child nodes

I have this XML file

Autodesk parts library XML example

I need to search for the <deviceset> element by its name (for example name="DB_") and replace its children subtree <technologies> with updated data.

So far I made function that returns MSXML2.IXMLDOMElement <technologies> with correct structure, but I have no clue how to search and replace in the main document.

I'm trying this approach

'Select everything from table Interlink - This table contains element's names
Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset("SELECT * FROM Interlink")

'Create new document and load the file
Dim oDoc As DOMDocument60
Set oDoc = New DOMDocument60
oDoc.async = False
oDoc.Load CurrentProject.Path & "\JLC_pattern.xml"

Dim Tech As IXMLDOMElement      'I can set this to contain updated <technologies> subtree
'is it better to use IXMLDOMNode? or IXMLDOMDocumentFragment?

Dim devSets As IXMLDOMNodeList  'Collection ?
Dim devSet As IXMLDOMNode       'Node?


'Loop through the recordset, search for elements and replace the subtree <technologies>
Do Until RS.EOF
    'Recordset now contains the deviceset name attribute
    Debug.Print RS.Fields("lbrDeviceSetName")  ' first record contains "DB_"
    
    'I can't find the right method to find the node or collection
    'I have tried:
    Set devSets = oDoc.getElementsByTagName("deviceset")   'and
    Set devSets = oDoc.selectNodes("//eagle/drawing/library/devicesets/deviceset")
    'but devSets collection is always empty
    For Each devSet In devSets
        Debug.Print devSet.baseName ' this does not loop
    Next devSet

    'I made a function that returns IXMLDOMNode with needed data structure
    'Once I find the node I need to replace the subtree
    'and move to the next deviceset name
    RS.MoveNext
Loop
'Save the modified XML document to disk
oDoc.Save CurrentProject.Path & "\SynthetizedDoc.xml"
RS.Close

'Cleanup...

It may be easier to loop through the collection of nodes and search the recordset instead of looping through the recordset and search the nodes.

Can anyone give me a clue please?

EDIT: I have expanded the VBA code with for each loop

Pattern XML is here JLC_Pattern.xml

EDIT 2: The <technologies> subtree can be quite huge. I don't want to overwhelm this post by code. I have a function getTechnology(tech as string) as IXMLDOMElement that pulls data from DB. Function output content can be downloaded here: IXMLDOMElement.xml The issue is not this function, I just don't know how to insert this output into the correct place of the oDoc

Solution

  • This works for me:

    'Create new document and load the file
Dim oDoc As DOMDocument60
Dim devSet As IXMLDOMNode

Set oDoc = New DOMDocument60
oDoc.async = False

'https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms762632(v=vs.85)
oDoc.SetProperty "ProhibitDTD", False 'needed for MSXML6

oDoc.validateOnParse = False 'or get a DTD-related error
                             '"The element 'eagle' is used but not declared in the DTD/Schema."

'always test for load errors
If Not oDoc.Load("C:\Tester\JLC_pattern.xml") Then
    Debug.Print oDoc.parseError.reason
    Exit Sub
End If

'select a single node based on its name attribute value
Set devSet = oDoc.SelectSingleNode("/eagle/drawing/library/devicesets/deviceset[@name='DB_']")
If Not devSet Is Nothing Then
    Debug.Print devSet.XML
    'work with devSet child nodes...
Else
    Debug.Print "node not found"
End If