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
at

2 comments:

  1. nico50387:42 PM

    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)

    ReplyDelete
  2. Alex Dybenko11:17 AM

    Thanks for update, Nico!

    ReplyDelete