Xpath works for MSXML2.DOMDocument, but not for MSXML2.DOMDocument.6.0

Question

For parsing my XML documents in MS Excel VBA, I have to use MSXML2.DOMDocument.6.0.

XPath statements like the following:

Public xml_document As Object
Public xml_namespace_uri As String
...
    xml_namespace_uri = "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"

    Set xml_document = CreateObject("MSXML2.DOMDocument")

    xml_document.async = False
    xml_document.validateOnParse = True
    xml_document.LoadXML _
        "<?xml version=""1.0"" encoding=""UTF-8""?>" & _
        "<Document xmlns=""" & xml_namespace_uri & """ " & _
        "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""/>"

    xml_document.SelectSingleNode("/Document").appendChild _
        xml_document.createNode(1, "CstmrDrctDbtInitn", xml_namespace_uri)

work fine, but as soon as I replace

    Set xml_document = CreateObject("MSXML2.DOMDocument")

by

    Set xml_document = CreateObject("MSXML2.DOMDocument.6.0")

the XPath statement fails and the containing sub is exited. Can anybody explain what I am doing wrong here?

2020-09-28 12:00:00

Having read all suggestions and remarks, I have extended Michael's working example as follows:

Sub XmlText()

    Dim xml_namespace_uri As String
    Dim xml_document As Object
    Dim docnode01 As Object
    Dim docnode02 As Object

    xml_namespace_uri = "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
    
    Set xml_document = CreateObject("MSXML2.DOMDocument.6.0")
    xml_document.setProperty "SelectionNamespaces", "xmlns:doc='" & xml_namespace_uri & "'"
    
    xml_document.async = False
    xml_document.validateOnParse = True
    xml_document.LoadXML _
        "<?xml version=""1.0"" encoding=""UTF-8""?>" & _
        "<Document xmlns:doc=""" & xml_namespace_uri & """ " & _
        "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""/>"

    Set docnode01 = xml_document.SelectSingleNode("/Document")
    docnode01.appendChild _
        xml_document.createNode(1, "CstmrDrctDbtInitn", "xmlns:doc='" & xml_namespace_uri & "'")

    Set docnode02 = xml_document.SelectSingleNode("/Document/CstmrDrctDbtInitn")
    docnode02.appendChild _
        xml_document.createNode(1, "GrpHdr", "xmlns:doc='" & xml_namespace_uri & "'")

    Debug.Print xml_document.XML

End Sub

Now, the program gets a hickup at the second appendChild statement and produces the error message: "Error 91 while executing: Object variable or block variable With has not been set" (freely translated from Dutch).

Could this be the "Dim As Object" statements, not being specific enough? I have tried several other data types, all producing some error message.

2020-09-28 12:10:00

By the way, adding the following statement doesn't solve this issue either:

    xml_document.setProperty "SelectionLanguage", "XPath"

2020-09-28 13:05

In answer to Parfait's question, I have tried to follow the line of reasoning in "https://stackoverflow.com/questions/58026296/why-does-parsing-xml-document-using-msxml-v3-0-work-but-msxml-v6-0-doesnt". I have added namespace prefixes to XPath expressions as follows:

    Set docnode01 = xml_document.SelectSingleNode("/doc:Document")

but then the next appendChild statement fails: "object variable not set". With apologies for me probably not fully understanding what I am doing, the following attempt also fails:

    Set docnode01 = xml_document.SelectSingleNode("/xmlns:doc:Document")

Don't tell us something fails -- tell us how it fails. All diagnosis starts with a description of the symptoms. It looks like a namespace issue, but I don't know MSXML enough to be any more specific than that. — Michael Kay, Sep 24 '20 at 14:44
You might be interested in added hints to versioning history at [XML parse/VBA Excel - added hints](https://stackoverflow.com/questions/53558110/xml-parse-vba-excel-function-trip-msxml2-domdocument/53559474#53559474) @MichaelKay — T.M., Sep 26 '20 at 19:43
Is it still not working for you? I have tested this end-to-end as shown below in MSO 2016. — wp78de, Sep 27 '20 at 01:08
Does this answer your question? [Why does parsing XML document using MSXML v3.0 work, but MSXML v6.0 doesn't](https://stackoverflow.com/questions/58026296/why-does-parsing-xml-document-using-msxml-v3-0-work-but-msxml-v6-0-doesnt) — Parfait, Sep 27 '20 at 01:35
Thanks Michael, thanks Parfait, for your solutions and your attempts to get me running. Alas! No movement yet. I got Michael's solution running. This solution, however, does not pass the scrutiny of parsing it against the "pain.008.001.02.xsd": no namespace attribute allowed in a "CstmrDrctDbtInitn" element. At this point, I am really very confused! Not the right state of mind to solve a problem. I certainly hope that I am not asking to much, if I ask you to have a another look, especially into the last remarks that I have added to my post. — Sunclass, Sep 28 '20 at 13:19

wp78de · Answer 1 · 2020-09-27T01:07:14.413

As hinted by @MichaelKay the issue does appear to be namespace handling between the MSXML versions.

Setting the SelectionNamespaces xml_doc.property with a prefix does the trick for me: xmlns:doc

Sub XmlText()
    xml_namespace_uri = "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
    
    Set xml_document = CreateObject("MSXML2.DOMDocument.6.0")
    xml_document.SetProperty "SelectionNamespaces", "xmlns:doc='" & xml_namespace_uri & "'"
    
    xml_document.async = False
    xml_document.validateOnParse = True
    xml_document.LoadXML _
        "<?xml version=""1.0"" encoding=""UTF-8""?>" & _
        "<Document xmlns:doc=""" & xml_namespace_uri & """ " & _
        "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""/>"
    
    Set docNode = xml_document.SelectSingleNode("/Document")
    docNode.appendChild _
        xml_document.createNode(1, "CstmrDrctDbtInitn", "xmlns:doc='" & xml_namespace_uri & "'")
    MsgBox (docNode.XML)
End Sub

Shows in a message box:

<Document xmlns:doc="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <CstmrDrctDbtInitn xmlns="xmlns:doc='urn:iso:std:iso:20022:tech:xsd:pain.008.001.02'"/>
</Document>

FYI c.f. my added versioning hints at [XML parse via VBA](https://stackoverflow.com/questions/53558110/xml-parse-vba-excel-function-trip-msxml2-domdocument/53559474#53559474) +:) — T.M., Sep 26 '20 at 18:48

Xpath works for MSXML2.DOMDocument, but not for MSXML2.DOMDocument.6.0

1 Answers1