Failed to parse XML: Whitespace expected

Question

I have a simple VBA function which makes sends a POST to a remote server. The post body is an XML document.

Sub testmessage()
Dim sXML As String, sURL As String, sResponse As String
Dim WinHttpReq As Object

Set WinHttpReq = CreateObject("MSXML2.ServerXMLHTTP")

sXML = "<?xml version=""1.0"" encoding=""ISO-8859-1""?>"
sXML = sXML & "<!DOCTYPE pnet_imessage_send PUBLIC ""-//PeopleNet//pnet_imessage_send"""
sXML = sXML & " ""http://open.peoplenetonline.com/dtd/pnet_imessage_send.dtd""> "
sXML = sXML & "<pnet_imessage_send>"
sXML = sXML & "  <cid>20</cid>"
sXML = sXML & "  <pw>password</pw>"
sXML = sXML & "  <vehicle_number>123</vehicle_number>"
sXML = sXML & "  <deliver>now</deliver>"
sXML = sXML & "  <freeform_message>This is Tim's test message.</freeform_message>"
sXML = sXML & "</pnet_imessage_send>"

'Test url'
'sURL = "https://open.pfmlogin.com/scripts/postp.dll"

'Live url
sURL = "https://oi.pfmlogin.com/scripts/open.dll"

WinHttpReq.Open "POST", sURL, False
WinHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded;Charset=ISO-8859-1"
WinHttpReq.Send sXML
sResponse = WinHttpReq.responseText

Debug.Print sResponse
End Sub

sXML contains this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE pnet_imessage_send PUBLIC "-//PeopleNet//pnet_imessage_send" 
  "http://open.peoplenetonline.com/dtd/pnet_imessage_send.dtd"> 
<pnet_imessage_send>
  <cid>20</cid>
  <pw>password</pw>
  <vehicle_number>123</vehicle_number>
  <deliver>now</deliver>
  <freeform_message>This is Tim's test message.</freeform_message> 
</pnet_imessage_send>

If I send this using Chrome's Postman plugin, it processes without issue. When posted with the VBA code, it returns an error XML failed to parse. The reported error was: File: . Line: 1 Col: 19 Error: Whitespace expected at Line: 1 Position: 19.

If I post the data to the test url (which just bounces back whatever gets posted to it), it shows up like this:

<?xml version="1.0"
encoding="ISO-8859-1"
><!DOCTYPE pnet_imessage_send PUBLIC "-//PeopleNet//pnet_imessage_send"
http://open.peoplenetonline.com/dtd/pnet_imessage_send.dtd

I believe the server is not treating the linebreaks as whitespace, resulting in something that looks like this to the XML parser: <?xml version="1.0"encoding="ISO-8859-1"> (no space between "1.0" and encoding). Posts made with Postman do not have the unexpected line breaks. How can I post this data without VBA breaking up the prolog?

Your request header has content type `application/x-www-form-urlencoded` but you're not url-encoding your XML payload. — Tim Williams, Apr 16 '18 at 21:49
https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba — Tim Williams, Apr 16 '18 at 21:52
@TimWilliams commenting out the `setRequestHeader` line still produces the same `Error: Whitespace expected at Line: 1 Position: 19` — Tim, Apr 16 '18 at 22:02
Did you try leaving the header as-is and URL-encoding your XML? Also - exact same issue here (though different client platform) https://stackoverflow.com/questions/2908597/idhttp-post-method-delphi-2010 — Tim Williams, Apr 16 '18 at 22:15
Maybe I'm missing something, but I don't see you adding new lines anywhere in your `sXML = sXML & ...` concatenation section. Try appending `& chr(10)` at the points with a new line. Maybe at character 19, as the error message says, and see if it tells you that it expected whitespace at a new character index. — chillin, Apr 17 '18 at 06:04
I would also recommend URL encoding `sXML` prior to using `.send` (try `Application.EncodeURL(sXML)`) so that its contents are not corrupted during transit, and whilst keeping the `application/x-www-form-urlencoded` request header. — chillin, Apr 17 '18 at 06:06
@TimWilliams Darn good find on that Delphi code! It is exactly the same issue. I searched SO and googled for a while before posting. @chilln - yeah, that's the rub: I DON'T add any line breaks anywhere. Something in the send process is adding them for me (its not a bug...it's a feature!) I'm using Excel 2010 so `Application.EncodeURL` isn't available to me and I intend to port it to VBScript to run as a Scheduled Task. I saw several snippets for HTML/URL encoding strings in other posts. I'll give some a try. — Tim, Apr 17 '18 at 12:16

score 1 · Accepted Answer · answered Apr 17 '18 at 12:37

Credit for this answer goes to @TimWilliams. Find one of his posts and give it a +1 if it helps you.

As He and @Chillin pointed out, the form data needed to be url encoded (or encoded in a way that preserved whitespace...HTML encoding might have worked too, but I didn't try it). Simply setting the request header to application/xml did not preserve the whitespace.

I used the code below from this SO post: How can I URL encode a string in Excel VBA? (so go give it a +1 as well if this helped you)

Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

I then call this function after concatenating my sXML string like this:

sXML = sXML & "   <freeform_message>This is Tim's test message.</freeform_message>"
sXML = sXML & "</pnet_imessage_send>"
sXML = "service=imessage_send&xml=" & URLEncode(sXML, False)

And it works like a charm!

Failed to parse XML: Whitespace expected

1 Answers1