Fix XML encoding in VBA

Question

I have this code in VBA

Sub MySub ()

Dim body As String

body= "<?xml version=""1.0"" encoding="utf-8""?>" & _
           "<GetOrdersRequest xmlns=""urn:ebay:apis:eBLBaseComponents"">" & 
            "<ErrorLanguage>en_US</ErrorLanguage>"

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1)
URL = "https://api.testurl.com/ws/api.dll"

objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "X-API-SITEDID", "0"
objHTTP.setRequestHeader "X-API-REQUEST-Encoding", "XML"
objHTTP.setRequestHeader "X-API-COMPATIBILITY-LEVEL", "967"

objHTTP.send (body)

Set objXML = New MSXML2.DOMDocument
objXML.async = False
objXML.LoadXML (objHTTP.ResponseText)
objXML.Save "C:\Users\Dan\Desktop\MySubOutput.xhtml"

End Sub

here is output:

1) how do I fix those characters in the xml response? (before parsing and pulling data from it)

the actual output for this node should be "32ième"

2) once that is done, how do I remove all accents from characters?

I mean à,ḗ,ḯ,ǿ,ǘ,ḉ,ǹ >> o a,e,i,o,u,c,n

Answer 1

Consider using XSLT, the transformation language used to manipulate XML documents. XSLT maintains the translate() function allowing you to replace characters (not words). And VBA's MSXML library which you are already using can run XSLT 1.0 scripts. The below script will run your replacement anywhere the accented characters show up. Example conversions:

32iÃme avenue --> 32ième avenue OR 32iÃàḗḯǿǘḉǹme avenue --> 32ièaeiounme avenue

XSLT script (save as a separate .xsl file and save content in a UTF-8 encoding format, not ANSI)

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="utf-8" indent="yes" />
<xsl:strip-space elements="*"/>

  <!-- Identity Transform to copy all of doc as is -->
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <!-- replace accented character -->
  <xsl:template match="text()">
     <xsl:value-of select="translate(., 'Ãàḗḯǿǘḉǹ', 'èaeioun')"/>
  </xsl:template>

</xsl:transform>

VBA (where latter references should point to newXML and not objXML)

Set objXML = New MSXML2.DOMDocument
objXML.async = False
objXML.LoadXML (objHTTP.ResponseText)
objXML.Save "C:\Users\Dan\Desktop\MySubOutput.xhtml"

Set objXSL = New MSXML2.DOMDocument
objXSL.async = False
objXSL.Load "C:\Path\To\XSLT\Script.xsl"

' TRANSFORMING objXML to newXML
Set newXML = New MSXML2.DOMDocument
objXML.transformNodeToObject objXSL, newXML

' NOTICE newXML REFERENCES
XmlNamespaces = "xmlns:doc='urn:ebay:apis:eBLBaseComponents'"
newXML.setProperty "SelectionNamespaces", XmlNamespaces
newXML.setProperty "SelectionLanguage", "XPath"

Dim xItemList As IXMLDOMNodeList
Set xItemList = newXML.DocumentElement.SelectNodes("//doc:Transaction")
...

XSLT can even be embedded as a VBA string and not in a file. Be sure to escape double quotes and instead of objXSL.Load you would use objXSL.LoadXML (just as you did with http response). And the reason it is similar, is that XSLT files are well-formed XML files which carry scripting instructions!