MS Access VBA用msxml2.DOMDocument.6.0发起XML POST请求报500错误求助

核心错误定位 #

你代码里最严重的问题是用错了DOMDocument的加载方法：Load 方法仅用于加载本地/网络XML文件路径，加载XML格式的字符串必须使用 LoadXML 方法。你当前的写法会导致DOMDocument无法正确解析你拼接的XML内容，发送给服务端的是无效请求，直接触发500错误。

对应问题解答 #

1. 代码错误排查
   除了上述的Load方法使用错误外，还有两处可优化的校验逻辑缺失：

• 缺少XML加载结果校验，无法确认拼接的XML是否被正确解析
• 缺少HTTP请求状态码校验，无法区分服务端错误还是本地请求错误
  修改后的可运行代码见文末。

2. XML构造方式是否有误
   你手动拼接的XML字符串语法没有明显问题，错误出在后续的加载环节。如果担心XML格式问题，可以在加载完成后输出objXML.XML到立即窗口，和你拼接的字符串做对比确认。

3. 请求头相关问题
   XML请求体本身不需要包含HTTP头信息，你当前设置的Content-Type、Authorization都是标准HTTP请求头，作用是告知服务端你发送的内容格式、身份凭证，设置逻辑完全正确，和响应要求无关。

4. objXML.async = False是否必需
   该配置作用是设置DOMDocument加载XML时使用同步模式，等待加载完成后再执行后续代码。如果你是加载本地拼接的XML字符串，该配置不是必需项；如果后续有加载外部XML文件的需求，建议保留该配置避免拿到未完成加载的内容。

修改后代码 #

Sub MFGetRate()
    ' 需先在工具-引用中添加 Microsoft XML v6.0 引用
    Dim sGetResult              As String
    Dim httpObject              As Object
    Dim objXML                  As Object
    Dim sXML                    As String
    Dim apiURL                  As String
    
    Set objXML = CreateObject("msxml2.DOMDocument.6.0")
    Set httpObject = CreateObject("msxml2.serverxmlhttp.6.0")
    
    objXML.async = False
    ' 关闭校验避免非标准XML报错
    objXML.validateOnParse = False
    
    ' 构造XML请求体
    sXML = "<rate>"
    sXML = sXML & "<account>"
    sXML = sXML & "<code>XXXX</code>"
    sXML = sXML & "</account>"
    sXML = sXML & "<serviceLevel>"
    sXML = sXML & "<code>M2H</code>"
    sXML = sXML & "</serviceLevel>"
    sXML = sXML & "<origin>"
    sXML = sXML & "<freightRequiredDateTime>2021-11-04T17:33:10</freightRequiredDateTime>"
    sXML = sXML & "<freightRequiredDateTimeZone>New Zealand Standard Time</freightRequiredDateTimeZone>"
    sXML = sXML & "<address>"
    sXML = sXML & "<suburb>Rosedale</suburb>"
    sXML = sXML & "<postCode>0000</postCode>"
    sXML = sXML & "<town/>"
    sXML = sXML & "<city>Auckland</city>"
    sXML = sXML & "<stateCode/>"
    sXML = sXML & "<countryCode>NZ</countryCode>"
    sXML = sXML & "</address>"
    sXML = sXML & "</origin>"
    sXML = sXML & "<destination>"
    sXML = sXML & "<address>"
    sXML = sXML & "<suburb>XXXXXX</suburb>"
    sXML = sXML & "<postCode>0000</postCode>"
    sXML = sXML & "<town/>"
    sXML = sXML & "<city>XXXXXX</city>"
    sXML = sXML & "<stateCode/>"
    sXML = sXML & "<countryCode>NZ</countryCode>"
    sXML = sXML & "</address>"
    sXML = sXML & "</destination>"
    sXML = sXML & "<freightDetails>"
    sXML = sXML & "<units>1</units>"
    sXML = sXML & "<packTypeCode>CTN</packTypeCode>"
    sXML = sXML & "<height/>"
    sXML = sXML & "<length/>"
    sXML = sXML & "<width/>"
    sXML = sXML & "<weight>30</weight>"
    sXML = sXML & "<volume>.1</volume>"
    sXML = sXML & "</freightDetails>"
    sXML = sXML & "</rate>"
    
    ' 注意这里用LoadXML加载字符串，不是Load
    objXML.LoadXML sXML
    
    ' 校验XML加载是否成功
    If objXML.parseError.errorCode <> 0 Then
        Debug.Print "XML解析错误：" & objXML.parseError.reason
        Exit Sub
    End If
    
    apiURL = "https://api.mainfreight.com/transport/1.0/Customer/Rate?region=NZ"
    httpObject.Open "POST", apiURL, False
    httpObject.setRequestHeader "Content-Type", "application/xml; charset=utf-8"
    httpObject.setRequestHeader "Authorization", "Secret 替换为你的实际密钥"
    httpObject.Send objXML.XML
    
    ' 校验HTTP请求状态
    If httpObject.Status = 200 Then
        sGetResult = httpObject.responseText
        Debug.Print "请求成功：" & sGetResult
    Else
        Debug.Print "请求失败，状态码：" & httpObject.Status & "，返回内容：" & httpObject.responseText
    End If
    
    ' 释放对象
    Set objXML = Nothing
    Set httpObject = Nothing
End Sub

内容的提问来源于stack exchange，提问作者Dean Graham