Solved

Access function to clean xml file

Posted on 2013-12-03
6
596 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 119

Expert Comment

by:Rey Obrero
ID: 39692698
how are you creating the xml file?
0
 
LVL 119

Expert Comment

by:Rey Obrero
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now