修复VBA中的XML编码 [英] Fix XML encoding in VBA

问题描述

我在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

这里是输出:

1)如何在xml响应中修复这些字符? (在解析和提取数据之前)

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

此节点的实际输出应为32ième"

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

2)完成后，如何删除字符中的所有重音符号?

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

我的意思是 à，ḗ，ḯ，ǿ，ǘ，ḉ，ḉ，ǹ>> o a，e，i，o，u，c，n

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

推荐答案

考虑使用 XSLT ，用于处理XML文档的转换语言. XSLT维护 translate() 函数，该功能允许您可以替换字符(不是个字).您已经在使用的VBA的MSXML库可以运行XSLT 1.0脚本.下面的脚本将在出现重音符号的任何地方运行您的替换脚本.转换示例:

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或 32iÃàḗḯǿǘḉǹme avenue-> 32ièaeiounme avenue

XSLT 脚本(另存为.xsl文件，并以UTF-8编码格式保存内容，不是 ANSI)

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 (其中后面的引用应指向newXML而不是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甚至可以作为VBA字符串而不是文件嵌入.确保使用双引号，并且使用objXSL.LoadXML代替objXSL.Load(就像使用http响应一样).而且之所以相似，是因为XSLT文件 是格式正确的XML文件，带有脚本指令！

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!

这篇关于修复VBA中的XML编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！