AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

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, "&", "&")
  varText = Replace(varText, "'", "'")
  varText = Replace(varText, """", """)
  varText = Replace(varText, ">", ">")
  varText = Replace(varText, "<", "&lt;")
  XMLSpecialChars = varText
End Function

Labels:

2 Comments:

Blogger 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  
Blogger Alex Dybenko said...

Thanks for update, Nico!

11:17 AM  

Post a Comment

<< Home