[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access function to clean xml file

Posted on 2013-12-03
6
Medium Priority
?
656 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

649 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