AccessBlog.net: Replace special characters for XML: Tips and tricks, news, links, downloads on Microsoft Access

Monday, December 26, 2011

Replace special characters for XML

If you are writing custom function for XML export – do not forget to replace predefined XML characters, else XML parsing function may fail. There are only 5 characters, so function looks pretty simple:

Function XMLSpecialChars(ByVal varText As Variant) As String 
  varText = varText & "" 
  varText = Replace(varText, "&", "&amp;") 
  varText = Replace(varText, "'", "&apos;") 
  varText = Replace(varText, """", "&quot;") 
  varText = Replace(varText, ">", "&gt;") 
  varText = Replace(varText, "<", "&lt;") 
  XMLSpecialChars = varText 
End Function

Labels: VBA

2 Comments:

nico5038 said...: Hi Alex,

Thanks for posting this. Just what I needed, but.....

Besides the Special Characters XML uses I ran into trouble with our European special characters like ë

So I've expanded your function into:

Function XMLSpecialChars(ByVal varText As Variant) As String
'This function replaces the Special Characters used by XML
'and the non-standard ASCII characters (like ë) so the returned string
'will throw no error when opening the XML in a browser.

Dim i As Integer

'The XMLSpecialChars
varText = varText & ""
varText = Replace(varText, "&", "&")
varText = Replace(varText, "'", "'")
varText = Replace(varText, """", """)
varText = Replace(varText, ">", ">")
varText = Replace(varText, "<", "<")

'The ASCII SpecialChars

'Loop through the field and inspect every character to be between 0 and 127,
'If not, replace it by the string: &#;
For i = Len(varText) To 1 Step -1
If Asc(Mid(varText, i, 1)) > 127 Then
' replace character by it's ascii value and append the already inspected string
varText = Mid(varText, 1, i - 1) & "&#" & Asc(Mid(varText, i, 1)) & ";" & Mid(varText, i + 1)
End If
Next

XMLSpecialChars = varText

End Function

Regards,

Nic;o); 7:42 PM
Alex Dybenko said...: Thanks for update, Nico!; 11:17 AM