How to use Name Space in XML selectNodes and selectSingleNode

Question

I'm porting an VBA Access Application from Win7 to Win11 and I think there should be a way to do it more elegant. I have to read this .xml file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
   <Document 
 xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:camt.053.001.08 camt.053.001.08.xsd"
 xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.08"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <BkToCstmrStmt>
            <GrpHdr>
                <MsgId>MSG-C053-231229020607-01</MsgId>
                <CreDtTm>2023-12-29T14:06:07.386+01:00</CreDtTm>
                <AddtlInf>RECONSTRUCTION</AddtlInf>
            </GrpHdr>
            <Stmt>
                <Id>STM-C053-231229020607-01</Id>
                <ElctrncSeqNb>999999</ElctrncSeqNb>
                <CreDtTm>2023-12-29T14:06:07.386+01:00</CreDtTm>
                <FrToDt>
                    <FrDtTm>2023-11-29T00:00:00.000+01:00</FrDtTm>
                    <ToDtTm>2023-12-28T23:59:59.999+01:00</ToDtTm>
                </FrToDt>
                <CpyDplctInd>DUPL</CpyDplctInd>
                <Acct>
                    <Id>
                        <IBAN>CHxxxxxxxxxxxxxxxxx</IBAN>
                    </Id>
                    <Ccy>CHF</Ccy>
                    <Ownr>
                        <Nm>Entity</Nm>
                    </Ownr>
                    <Svcr>
                        <FinInstnId>
                            <Nm>Name of Bank</Nm>
                        </FinInstnId>
                    </Svcr>
                </Acct>
                <Bal>
            <Shortened> 
            </Shortened>
                 </Bal>
                <Bal>
            <Shortened> 
            </Shortened>
                </Bal>
                <Ntry>
            <Shortened> 
            </Shortened>
                </Ntry>
    
            </Stmt>
        </BkToCstmrStmt>
</Document>

In Win7 \Windows\system32\msxml6.dll was used and this code did a perfect job:

Sub ReadCamt053(strDPFE As String)
    
    Dim bankDownload As MSXML2.DOMDocument
      Set bankDownload = New MSXML2.DOMDocument
      bankDownload.validateOnParse = True
      bankDownload.Load strDPFE
      
      Dim groupHeader As IXMLDOMNode
      For Each groupHeader In bankDownload.selectNodes("Document/BkToCstmrStmt/GrpHdr")
        Debug.Print "Erzeugungsdatum(CreDtTm)", groupHeader.selectSingleNode("CreDtTm").Text
      Next groupHeader
      
      Dim accountStatement As IXMLDOMNode
      For Each accountStatement In bankDownload.selectNodes("Document/BkToCstmrStmt/Stmt")
        gIBAN = accountStatement.selectSingleNode("Acct/Id/IBAN").Text
        gSequenzNr = accountStatement.selectSingleNode("ElctrncSeqNb").Text
        ReadStatementEntries accountStatement.selectNodes("Ntry")
      Next accountStatement
End Sub

For the port to Win11 \Windows\SysWOW64\msxml6.dll got selected for XML, which requires the use of DOMDocument60 instead of DOMDocument. Using Google I found this from MS and assume I have to add a namespace in order to read XML. I did so, but I was not able to find an elegant way to add the name space for groupHeader.selectSingleNode("CreDtTm").Text - groupHeader.selectSingleNode("/ns:CreDtTm").Text results in an Error 91, using groupHeader.selectSingleNode("/bk:Document/bk:BkToCstmrStmt/bk:GrpHdr/bk:CreDtTm").Text works, but seems to be very bad to read....

Sub ReadCamt053(strDPFE As String)
    
    Dim bankDownload As MSXML2.DOMDocument60
      Set bankDownload = New MSXML2.DOMDocument60
      bankDownload.validateOnParse = True
      bankDownload.SetProperty "SelectionNamespaces", "xmlns:ns='urn:iso:std:iso:20022:tech:xsd:camt.053.001.08'"
      bankDownload.Load strDPFE
      
      Dim groupHeader As IXMLDOMNode
      For Each groupHeader In bankDownload.selectNodes("/ns:Document/ns:BkToCstmrStmt/ns:GrpHdr")    
        Debug.Print "Erzeugungsdatum(CreDtTm)", groupHeader.selectSingleNode("CreDtTm").Text     '<---
      Next groupHeader
      
      Dim accountStatement As IXMLDOMNode
      For Each accountStatement In bankDownload.selectNodes("Document/BkToCstmrStmt/Stmt")
        gIBAN = accountStatement.selectSingleNode("Acct/Id/IBAN").Text
        gSequenzNr = accountStatement.selectSingleNode("ElctrncSeqNb").Text
        ReadStatementEntries accountStatement.selectNodes("Ntry")
      Next accountStatement
End Sub

My knowledge about .XML is very limited (trial an error). So here my 2 questions:

1. Is there a better way than groupHeader.selectSingleNode("/bk:Document/bk:BkToCstmrStmt/bk:GrpHdr/bk:CreDtTm")

2. The whole stuff regarding this name space seems pretty clumsy. Is there any way to do it without continously repeating the name space - I do have only this single Name Space, so it seems to be a huge overkill... The old stuff did the job and looked so much better...

Answer 1

1- If adding a namespace, then modify the code as following:

For Each groupHeader In bankDownload.selectNodes("ns:Document/ns:BkToCstmrStmt/ns:GrpHdr")
    Debug.Print "Erzeugungsdatum(CreDtTm)", groupHeader.selectSingleNode("ns:CreDtTm").Text
Next groupHeader
      
For Each accountStatement In bankDownload.selectNodes("ns:Document/ns:BkToCstmrStmt/ns:Stmt")
    gIBAN = accountStatement.selectSingleNode("ns:Acct/ns:Id/ns:IBAN").Text
    gSequenzNr = accountStatement.selectSingleNode("ns:ElctrncSeqNb").Text
    ReadStatementEntries accountStatement.selectNodes("ns:Ntry")
Next accountStatement

OR

2- Remove the namespace(s) from the document so the <Document xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:camt.053.001.08 camt.053.001.08.xsd" xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.08" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> becomes <Document> and use the original code as before:

Const s1 = "xsi:schemaLocation=""urn:iso:std:iso:20022:tech:xsd:camt.053.001.08 camt.053.001.08.xsd"""
Const s2 = "xmlns=""urn:iso:std:iso:20022:tech:xsd:camt.053.001.08"""
Const s3 = "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"""

Dim bankDownload As New MSXML2.DOMDocument60
bankDownload.validateOnParse = False
bankDownload.Load Path_To_XML_File
Dim xml As String
xml = bankDownload.xml
xml = Replace(xml, s1, "")
xml = Replace(xml, s2, "")
xml = Replace(xml, s3, "")
bankDownload.loadXML xml

Dim groupHeader As IXMLDOMNode
For Each groupHeader In bankDownload.selectNodes("Document/BkToCstmrStmt/GrpHdr")
    Debug.Print "Erzeugungsdatum(CreDtTm)", groupHeader.selectSingleNode("CreDtTm").Text
Next groupHeader
      
Dim accountStatement As IXMLDOMNode
     
For Each accountStatement In bankDownload.selectNodes("Document/BkToCstmrStmt/Stmt")
    gIBAN = accountStatement.selectSingleNode("Acct/Id/IBAN").Text
    gSequenzNr = accountStatement.selectSingleNode("ElctrncSeqNb").Text
    ReadStatementEntries accountStatement.selectNodes("Ntry")
Next accountStatement