How to load an XML file with an XML declaration in Outlook VBA?

Question

I have an Outlook macro within the integrated development environment inside Outlook 2017.

The macro searches through an xml file for an entry containing the sender's mail address, to find a corresponding "No.".

This works if an xml file contains no xml declaration at the start of the file.

The files I get all start with this xml tag at the beginning, and my application is unable to process the file.

The code I am using to parse the file looks like this:

Private Function GetVendorNoFromFile(filepath As String, oMail As Outlook.MailItem) As String

    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    oXMLFile.Load (filepath)

    Set Vendors = oXMLFile.GetElementsByTagName("Vendor")
    For Each vendor In Vendors
        If LCase(vendor.SelectSingleNode("E-Mail").Text) = LCase(GetSMTPMailAdress(oMail)) Then
            GetVendorNoFromFile = vendor.SelectSingleNode("No.").Text
            Exit For
        End If
    Next
End Function

It works if the file looks like this:

<Vendors>
    <Vendor>
        <No.>No1</No.>
        <E-Mail>Mail1</E-Mail>
    </Vendor>
    <Vendor>
        <No.>No2</No.>
        <E-Mail>Mail2</E-Mail>
    </Vendor>
</Vendors>

But it doesn't if the file looks like this:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<Vendors>
    <Vendor>
        <No.>No1</No.>
        <E-Mail>Mail1</E-Mail>
    </Vendor>
    <Vendor>
        <No.>No2</No.>
        <E-Mail>Mail2</E-Mail>
    </Vendor>
</Vendors>

To be more precise: The variable "oXMLFile" has an attribute "childNodes", which has a count of one, containing the tag "Vendors" for the first xml file, but it has a count of zero if I use the second xml file with the xml declaration.

I tried adding either of these lines before the loading of the file

oXMLFile.async = False
oXMLFile.validateOnParse = False

But it didn't change anything.

All the examples I could find online do it the same way as I do, but they appear to work with a file containing an xml declaration.

score 0 · Accepted Answer · answered Nov 15 '19 at 10:20

I was able to find the cause of the issue:

I had created a sample xml file and had copied the content of the xml files I will be using into that newly created sample file. However the original files were formatted in "UCS-2 LE-BOM" (according to Notepad++), but the file I created from scratch was formatted as UTF-8. And the xml declaration I copied over without thinking says "encoding="UTF-16".

If I change the xml declaration to say "UTF-8" in my sample file it works. The same is true if I use one of the original files with the declaration saying "UTF-16" and the aforementioned formatting. My variable "oXMLFile" now has 2 "ChildNodes", one being the xml declaration tag, the other being "Vendors".

How to load an XML file with an XML declaration in Outlook VBA?

1 Answers1