Solved

Access function to clean xml file

Posted on 2013-12-03
6
620 Views
Last Modified: 2013-12-14
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
Comment
Question by:HKFuey
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39692698
how are you creating the xml file?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39692731
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
 

Author Comment

by:HKFuey
ID: 39692737
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 39692771
try using the replace function

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

or

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

Accepted Solution

by:
HKFuey earned 0 total points
ID: 39692861
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
 

Author Closing Comment

by:HKFuey
ID: 39718462
Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question