• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

Access function to clean xml file

Does anyone know where I can find a function to clean invalid characters from an XML file?

I am creating the xml from a sql tables.
0
HKFuey
Asked:
HKFuey
  • 3
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
how are you creating the xml file?
0
 
Rey Obrero (Capricorn1)Commented:
here is sample code to create xml file using recordset


Dim rst As ADODB.Recordset, cn As ADODB.Connection
Dim strSql As String, strCn As String, strFileName As String
Dim objDom As Object
Set objDom = CreateObject("msxml2.DOMDocument")

strSql = "SELECT * from tableName"


WorkStation = Environ("computername")
'open connection
Set cn = New ADODB.Connection
    cn.CommandTimeout = 180
    cn.ConnectionTimeout = 180
strCn = "DRIVER={sql server};" & _
        "DATABASE=" & DatabaseName & ";" & _
        "SERVER=" & ServerName & ";" & _
        "WSID=" & WorkStation & ";" & _
        "Trusted_Connection=YES;"
cn.Open strCn
Set rst = New ADODB.Recordset

rst.Open strSql, cn, adOpenStatic, adLockReadOnly, adCmdText
Set cn = Nothing

rst.Save objDom, adPersistXML

objDom.Save CurrentProject.Path & "\" & strFileName

rst.Close
0
 
HKFueyAuthor Commented:
It's a bit complicated, coming from various record sets. Customer, Addresses, Driver Name, Stop number etc.

The xml looks ok if I open in IE, but as soon as the customer name has an ampersand it will not load.

I start with this: -
      Open Filename For Output As #1 ' .xml
      ProcessXML

Open in new window


Here is a section of the ProcessXML bit: -
            Print #1, Tab(12); "<Account>"
            
                Print #1, Tab(14); "<Code>"; Ccode; "</Code>"
                Print #1, Tab(14); "<GroupAccountCode>"; Ccode; "</GroupAccountCode>"
                Print #1, Tab(14); "<Name>"; Cname; "</Name>"
                Print #1, Tab(14); "<ContactName>"; Coname; "></ContactName>"
                Print #1, Tab(14); "<Address1>"; Add1; "</Address1>"
                Print #1, Tab(14); "<Address2>"; Add2; "</Address2>"
                Print #1, Tab(14); "<Address3>"; Add3; "</Address3>"
                Print #1, Tab(14); "<Address4>"; Add4; "</Address4>"
                Print #1, Tab(14); "<Address5>"; Add5; "</Address5>"
                Print #1, Tab(14); "<PostCode>"; AddPC; "</PostCode>"
                Print #1, Tab(14); "<ContactNumber>"; Tel; "</ContactNumber>"
                Print #1, Tab(14); "<StartWindow>08:30</StartWindow>"
                Print #1, Tab(14); "<EndWindow>17:00</EndWindow>"
                Print #1, Tab(14); "<Latitude>"; Latt; "</Latitude>"
                Print #1, Tab(14); "<Longitude>"; Lng; "</Longitude>"
                Print #1, Tab(14); "<UseGroupLocation>True</UseGroupLocation>"
             Print #1, Tab(12); "</Account>"

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rey Obrero (Capricorn1)Commented:
try using the replace function

replace(coName,,chr(38)," ")

or

replace(coName,,chr(38)," And ")
0
 
HKFueyAuthor Commented:
I found a function that does it, my xml now looks OK : ))

http://www.access-programmers.co.uk/forums/showthread.php?t=185018

Function XMLit(szIn As Variant) As String

    If Not IsNull(szIn) Then
        XMLit = Replace(szIn, "&", "&amp;")
        XMLit = Replace(XMLit, "£", "£")
        XMLit = Replace(XMLit, "“", "“")
        XMLit = Replace(XMLit, "€", "€")
        XMLit = Replace(XMLit, "„", " ")
        XMLit = Replace(XMLit, Chr$(148), "”")
        XMLit = Replace(XMLit, Chr$(153), "™")
        XMLit = Replace(XMLit, Chr$(133), "…")
        XMLit = Replace(XMLit, Chr$(137), "‰")
        XMLit = Replace(XMLit, Chr$(144), "")
        XMLit = Replace(XMLit, Chr$(145), "‘")
        XMLit = Replace(XMLit, Chr$(146), "’")
        XMLit = Replace(XMLit, Chr$(147), "“")
        XMLit = Replace(XMLit, Chr$(148), "”")
        XMLit = Replace(XMLit, Chr$(149), "•")
        XMLit = Replace(XMLit, Chr$(150), "–")
        XMLit = Replace(XMLit, Chr$(151), "—")
        XMLit = Replace(XMLit, Chr$(152), "˜")
        XMLit = Replace(XMLit, Chr$(153), "™")
        XMLit = Replace(XMLit, Chr$(154), "š")
        XMLit = Replace(XMLit, Chr$(155), "›")
        XMLit = Replace(XMLit, Chr$(156), "œ")
        XMLit = Replace(XMLit, Chr$(157), "")
        XMLit = Replace(XMLit, Chr$(158), "ž")
        XMLit = Replace(XMLit, Chr$(159), "Ÿ")
        XMLit = Replace(XMLit, Chr$(160), " ")
        XMLit = Replace(XMLit, Chr$(162), "&cent;")
        XMLit = Replace(XMLit, """", "&quot;")
        XMLit = Replace(XMLit, Chr$(163), "£")
        XMLit = Replace(XMLit, Chr$(164), "¤")
        XMLit = Replace(XMLit, Chr$(165), "¥")
        XMLit = Replace(XMLit, Chr$(166), "¦")
        XMLit = Replace(XMLit, Chr$(167), "§")
        XMLit = Replace(XMLit, Chr$(168), "¨")
        XMLit = Replace(XMLit, Chr$(169), "©")
        XMLit = Replace(XMLit, Chr$(170), "ª")
        XMLit = Replace(XMLit, Chr$(171), "«")
        XMLit = Replace(XMLit, Chr$(172), "¬")
        XMLit = Replace(XMLit, Chr$(173), "­")
        XMLit = Replace(XMLit, Chr$(174), "®")
        XMLit = Replace(XMLit, Chr$(176), "°")
        XMLit = Replace(XMLit, Chr$(177), "±")
        XMLit = Replace(XMLit, Chr$(178), "²")
        XMLit = Replace(XMLit, Chr$(179), "³")
        XMLit = Replace(XMLit, Chr$(180), "&H180;")
        XMLit = Replace(XMLit, Chr$(181), "&H181;")
        XMLit = Replace(XMLit, "<", "&lt;")
        XMLit = Replace(XMLit, ">", "&gt;")
        XMLit = Replace(XMLit, Chr$(182), "¶")
        XMLit = Replace(XMLit, Chr$(183), "·")
        XMLit = Replace(XMLit, Chr$(186), "º")
        XMLit = Replace(XMLit, Chr$(187), "»")
        XMLit = Replace(XMLit, Chr$(188), "¼")
        XMLit = Replace(XMLit, Chr$(189), "½")
        XMLit = Replace(XMLit, Chr$(190), "¾")
        XMLit = Replace(XMLit, Chr$(191), "¿")
        XMLit = Replace(XMLit, Chr$(192), "À")
        XMLit = Replace(XMLit, Chr$(193), "Á")
        XMLit = Replace(XMLit, Chr$(194), "Â")
        XMLit = Replace(XMLit, Chr$(195), "Ã")
        XMLit = Replace(XMLit, Chr$(196), "Ä")
        XMLit = Replace(XMLit, Chr$(197), "Å")
        XMLit = Replace(XMLit, Chr$(198), "Æ")
        XMLit = Replace(XMLit, Chr$(199), "Ç")
        XMLit = Replace(XMLit, Chr$(200), "È")
        XMLit = Replace(XMLit, Chr$(201), "É")
        XMLit = Replace(XMLit, Chr$(202), "Ê")
        XMLit = Replace(XMLit, Chr$(203), "Ë")
        XMLit = Replace(XMLit, Chr$(204), "Ì")
        XMLit = Replace(XMLit, Chr$(205), "Í")
        XMLit = Replace(XMLit, Chr$(206), "Î")
        XMLit = Replace(XMLit, Chr$(207), "Ï")
        XMLit = Replace(XMLit, Chr$(208), "Ð")
        XMLit = Replace(XMLit, Chr$(209), "Ñ")
        XMLit = Replace(XMLit, Chr$(210), "Ò")
        XMLit = Replace(XMLit, Chr$(211), "Ó")
        XMLit = Replace(XMLit, Chr$(212), "Ô")
        XMLit = Replace(XMLit, Chr$(213), "Õ")
        XMLit = Replace(XMLit, Chr$(214), "Ö")
        XMLit = Replace(XMLit, Chr$(215), "×")
        XMLit = Replace(XMLit, Chr$(216), "Ø")
        XMLit = Replace(XMLit, Chr$(217), "Ù")
        XMLit = Replace(XMLit, Chr$(218), "Ú")
        XMLit = Replace(XMLit, Chr$(219), "Û")
        XMLit = Replace(XMLit, Chr$(220), "Ü")
        XMLit = Replace(XMLit, Chr$(221), "Ý")
        XMLit = Replace(XMLit, Chr$(222), "Þ")
        XMLit = Replace(XMLit, Chr$(223), "ß")
        XMLit = Replace(XMLit, Chr$(224), "à")
        XMLit = Replace(XMLit, Chr$(225), "á")
        XMLit = Replace(XMLit, Chr$(226), "â")
        XMLit = Replace(XMLit, Chr$(227), "ã")
        XMLit = Replace(XMLit, Chr$(228), "ä")
        XMLit = Replace(XMLit, Chr$(229), "å")
        XMLit = Replace(XMLit, Chr$(230), "æ")
        XMLit = Replace(XMLit, Chr$(231), "ç")
        XMLit = Replace(XMLit, Chr$(232), "è")
        XMLit = Replace(XMLit, Chr$(233), "é")
        XMLit = Replace(XMLit, Chr$(234), "ê")
        XMLit = Replace(XMLit, Chr$(235), "ë")
        XMLit = Replace(XMLit, Chr$(236), "ì")
        XMLit = Replace(XMLit, Chr$(237), "í")
        XMLit = Replace(XMLit, Chr$(238), "î")
        XMLit = Replace(XMLit, Chr$(239), "ï")
        XMLit = Replace(XMLit, Chr$(240), "ð")
        XMLit = Replace(XMLit, Chr$(241), "ñ")
        XMLit = Replace(XMLit, Chr$(242), "ò")
        XMLit = Replace(XMLit, Chr$(243), "ó")
        XMLit = Replace(XMLit, Chr$(244), "ô")
        XMLit = Replace(XMLit, Chr$(245), "õ")
        XMLit = Replace(XMLit, Chr$(246), "ö")
        XMLit = Replace(XMLit, Chr$(247), "÷")
        XMLit = Replace(XMLit, Chr$(248), "ø")
        XMLit = Replace(XMLit, Chr$(249), "ù")
        XMLit = Replace(XMLit, Chr$(250), "ú")
        XMLit = Replace(XMLit, Chr$(251), "û")
        XMLit = Replace(XMLit, Chr$(252), "ü")
        XMLit = Replace(XMLit, Chr$(253), "ý")
        XMLit = Replace(XMLit, Chr$(254), "þ")
        XMLit = Replace(XMLit, Chr$(255), "ÿ")
        XMLit = Replace(XMLit, vbCrLf, "")
    Else
        XMLit = ""
    End If
    
        
End Function

Open in new window

0
 
HKFueyAuthor Commented:
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now