I am writing a small visual basic application to transfer data from an old SQL server database to new one.
( structure of old and new database is not similar)
so to do the transfer , I extract data row wise and create an xml string like this
strProductXML = "<Data><ProdID>" & strProdID & "</ProdID>" & _ "<ProdColor>" & rowSQL("ProdColor") & "</ProdColor><ProdUser>" & rowSQL("ProdData") & _ "</ProdUser><ProdYear>" & rowSQL("ProdYear") & "</ProdYear></Data>"
But the problem is rowSQL("ProdData") : this column can have values that contain less than(<) and greater than sign (>)
Proddata can have data like
"The front size measure is 6 inches <9rear face size."
"The units produced > year 1999"
" rear 9cm>front face at height of 8"
due to such data when I use this
in my code , i get error because those > and < signs make my XML string wrong.
Is there any way to escape it, while creating the XML String but Unescape it when this is being inserted back into database.
I use a stored procedure to insert my data. That stored procedure is called by my VB code.
Ideally, you should not create Xml document by concatenating strings. The best way to create an xml document is to use XmlWriter. It will ensure that the document you create is a valid Xml document. It will always encode characters if necessary/possible. Example:
writer.WriteStartElement("root"); writer.WriteString("abc > xyz ?"); writer.WriteEndElement();
results in the following:
<root>abc > xyz ?</root>
Which is a valid Xml document where the '>' character was replaced with the built-in entity >