Could not select elements nodes but can select attributes only .VBA -XML

Question

I have XML from IPC1752 schema where I have to fill values according to my excel data sheet. so I have to do these things

1.load XML into my VBA 2.change the desired value in the XML with values I stored in excel cell 3.save the modified XML to a folder

'Sub UpdateXML() Call fnUpdateXMLByTags End Sub

Function fnUpdateXMLByTags() Dim mainWorkBook As Workbook Dim wrsht As Worksheet

Set mainWorkBook = ActiveWorkbook
Set wrsht = mainWorkBook.Sheets("Sheet1")
wrsht.Activate

Dim oXMLFile As MSXML2.DOMDocument60
Set oXMLFile = New MSXML2.DOMDocument60
oXMLFile.async = False
oXMLFile.validateOnParse = False
XMLFileName = "Z:\IPC\IPC1752A_WK-200264-000 - Copy.xml"


For i = 3 To 5
  If Not IsEmpty(mainWorkBook.Sheets("Sheet1").Range("A" & i)) Then

        PartID = mainWorkBook.Sheets("Sheet1").Range("A" & i).Value
        PartName = mainWorkBook.Sheets("Sheet1").Range("B" & i).Value
        MaterialName = mainWorkBook.Sheets("Sheet1").Range("D" & i).Value
        MassAmount = mainWorkBook.Sheets("Sheet1").Range("F" & i).Value
        MassUnit = mainWorkBook.Sheets("Sheet1").Range("G" & i).Value
        Path = "D:\New folder\" & PartID & ".xml"

        If oXMLFile.Load(XMLFileName) Then
            Set PartIDNodes = oXMLFile.SelectNodes("//@itemNumber")
            Set PartNameNodes = oXMLFile.SelectNodes("//@itemName")
            Set MaterialNameNodes = oXMLFile.SelectNodes("//@name")
            Set MassAmountNodes = oXMLFile.SelectNodes("//@value")
            Set MassUnitNodes = oXMLFile.SelectNodes("//@UOM")
            PartIDNodes(0).NodeValue = Part_ID
            PartNameNodes(0).NodeValue = PartName
            MaterialNameNodes(5).NodeValue = MaterialName
            MassAmountNodes(1).NodeValue = MassAmount
            MassUnitNodes(1).NodeValue = MassUnit
            Set ParentNodes = oXMLFile.SelectNodes("//Substance")
           MsgBox ParentNodes.Length '->is showing zero
        End If


End If
Next i
End Function

here when I use selectnodes(attributes),its working [example:oXMLFile.SelectNodes("//@itemName")]

but not working when I am using selectnodes(elements) [example:oXMLFile.SelectNodes("//Substance")]

please please help.

Here is the XML https://www.jiocloud.com/s/?t=SzqFJhEABfsTQfZW&s=a2

What is the desired result? What exactly are you trying to extract from the xml that you need help with please? You want to select all elements? — QHarr, May 08 '18 at 15:44
my apologies as I had not explained well in the problem statement. suppose I had to add a node under a parent node I had to first select a parent and append a child node right? I am not able a select the desired element.but I am able to change the attribute value with the desired value. so where I am wrong while selecting elements? am I clear now bro? — pavan kumar, May 09 '18 at 04:49
here is the excel data I wanna fill in the XML https://www.jiocloud.com/s/?t=cpVDieMyifUMVEXJ&s=a2 — pavan kumar, May 09 '18 at 04:57
So you want to add an element node and then select the one you added? Or you want to select all element nodes or you want to select a specific element node? If selecting an existing can you give an example of one you would like to select from the above? Apologies. It is early morning here. Below I gave an answer which allows you to loop and find element nodes which I can update as I better understand what is missing. — QHarr, May 09 '18 at 05:09
thanks for your response : I want to select specific element node. For example i want to select NODE "SubstanceCategory" so Set ParentNodes = oXMLFile.SelectNodes("//SubstanceCategory") MsgBox ParentNodes.Length '->is showing zero means its not selecting . — pavan kumar, May 09 '18 at 06:18
hello @QHarr . please see my updated question. I almost there to my workable script. I owe you. — pavan kumar, May 10 '18 at 05:32
I answered your original question. This is now a new question and should be posted as such and then I will happily answer. You can rollback this question to its prior if you so choose. — QHarr, May 10 '18 at 06:32
Also, well done on finding your own solution based on name space suggestion. Please remember to post as an answer so others can learn from it. Plus you get rep points if it is upvoted. Might be worth adding a note as to why you chose that particular name space and the value of adding it over simply excluding the one I mentioned. — QHarr, May 10 '18 at 06:37
oh @QHarr, you thought me so much so far. I am very new to StackOverflow and I will post that as new question — pavan kumar, May 10 '18 at 15:11

QHarr · Answer 1 · 2018-05-09T09:40:33.673

This was driving my potty for a while until I realised it is name space related. You need to remove:

 xmlns="http://webstds.ipc.org/175x/2.0"

from the xml.

Then you can navigate with Xpath e.g.:

Public Sub test()
'Remove namespace info: ===>   xmlns="http://webstds.ipc.org/175x/2.0" 

    Dim xml As String, doc As MSXML2.DOMDocument60

    xml = [A1].Text

    Set doc = New MSXML2.DOMDocument60

    If Not doc.LoadXML(xml) Then
        Err.Raise doc.parseError.ErrorCode, , doc.parseError.reason
        Exit Sub
    End If

    Dim nodeList As Object

    Set nodeList = doc.SelectNodes("//Substance")
    Debug.Print nodeList.Length
End Sub

Other method:

This picks out element nodes based on NodeType. I had put your xml in cell A1.

Option Explicit

Public Sub test()
    Dim xml As String, doc As MSXML2.DOMDocument60

    xml = [A1].Text
    Set doc = New MSXML2.DOMDocument60

    If Not doc.LoadXML(xml) Then
        Err.Raise doc.parseError.ErrorCode, , doc.parseError.reason
        Exit Sub
    End If

    ''Use the nodeType property to only process element nodes
    Dim node As IXMLDOMElement
    For Each node In doc.DocumentElement.ChildNodes
        If node.NodeType = 1 Then
            Debug.Print node.nodeName
        End If
    Next node

End Sub

More fragile but descending the tree using the XPath:

/MainDeclaration[@xmlns="http://webstds.ipc.org/175x/2.0"]/Product[@unitType="Each"]/MaterialInfo/HomogeneousMaterialList/HomogeneousMaterial[@name="UNS C36000"]/SubstanceCategoryList

as a guide is:

Dim node As IXMLDOMElement
Set node = doc.DocumentElement.LastChild.ChildNodes(1).FirstChild.FirstChild.ChildNodes(1).ChildNodes(1) 

'^^ Above is: MainDeclaration> Product > MaterialInfo > HomogeneousMaterialList> HomogeneousMaterial > SubstanceCategoryList > SubstanceCategory

Dim i As Long
For i = 0 To node.ChildNodes.Length - 1
    Debug.Print node.ChildNodes(i).BaseName  '<== Substance
Next i

Reference:

Thanks a lot. it worked after removing xmlns. But actually, I don't know how important is that line to an XML file and suppose I want to retain it i.e select nodes without removing xmlns .is there any way ? — pavan kumar, May 09 '18 at 09:04
You can't then use XPath. See the second part of my answer about how to loop over and select element nodes. — QHarr, May 09 '18 at 09:05
I have given more information as a guide. Please let me know what else is missing from this. — QHarr, May 09 '18 at 09:35
Thanks, @Qharr. Meanwhile, I found another way "XmlNamespaces = "xmlns:d='http://webstds.ipc.org/175x/2.0'" oXMLFile.SetProperty "SelectionNamespaces", XmlNamespaces" then Set SubstanceCategoryNode = oXMLFile.SelectNodes("//d:SubstanceCategory") this is working too. — pavan kumar, May 10 '18 at 04:49

score 1 · Answer 2 · answered May 10 '18 at 15:13

I solved it by using namespace manager. thanks to @Qharr here it is

...part of code...

XmlNamespaces = "xmlns:d='http://webstds.ipc.org/175x/2.0'"
oXMLFile.SetProperty "SelectionNamespaces", XmlNamespaces


If IsEmpty(mainWorkBook.Sheets("Sheet1").Range("D" & i)) Then
Substancename = mainWorkBook.Sheets("Sheet1").Range("H" & i).Value
CASNumber = mainWorkBook.Sheets("Sheet1").Range("I" & i).Value
SubAmount = mainWorkBook.Sheets("Sheet1").Range("J" & i).Value
Set SubstanceCategoryNode = oXMLFile.SelectNodes("//d:SubstanceCategory")

Set Substancenode = oXMLFile.createElement("d:Substance")

Well done. +1. Why did you choose this particular name space? — QHarr, May 10 '18 at 16:18

Could not select elements nodes but can select attributes only .VBA -XML

2 Answers2