0

I am running into a character conversion problem when trying to export xml data from a SQL server table using Powershell 4.0. I'm using invoke-sqlcmd to run my sql server query, and found that many characters are not being interpreted correctly. For example, a > character is being received as &gt;, and a < character is being received as &lt;. Fractions, the degree symbol, formatted quotes, etc, are all doing the same thing. I wrote up several $string.replace commands below, which seems to fix about 80% of the problems, but when I spot check the results, I keep finding new characters that converted incorrectly.

Here is a sample of my SQL query and my replace statements: 

$sql = [my sql select statement] 
$result=Invoke-sqlcmd -Server $server -MaxCharLength 9000000 -Database $db -Username [myusername] -Password [mypassword] -Query $sql 

$string=$result[0] | out-string
$string=$string.replace('&lt;', '<')
$string=$string.replace('&gt;', '>')
$string=$string.replace('&amp;quot;', '&quot;')
$string=$string.replace('amp;', '')
$string=$string.replace('“', '“')
$string=$string.replace('â€', '”')
$string=$string.replace('’', "’")
$string=$string.replace('–', "–")
$string=$string.replace('& ', "&amp; ")
$string=$string.replace('½', "½")
$string=$string.replace('¼', "¼")
$string=$string.replace('�', "-")

So, I have two thoughts, and appreciate any feedback you may have:

1) I could keep exporting the data, spot checking, and writing a new replace statement (like above) when I find a problem. This seems to be time consuming and error prone. If I go this route, I'd hope to not reinvent the wheel. Has anyone found or created a list of commonly misinterpreted characters that I could start with?

2) The other option is to try to get to the root of the character conversion problem with my invoke-sqlcmd command so I don't have to do all of the replacements in the string. I have done a fair amount of research on this, but have not found anything to work properly. Does anyone have any thoughts for what I could change in my invoke-sqlcmd command to encode the data correctly?

I greatly appreciate any help that could be offered.

Thank you! Chris

Thom A
  • 88,727
  • 11
  • 45
  • 75
Chris Hansen
  • 1
  • That isn't a problem with the XML,or that characters aren't being "converted" incorrect. There are many "illegal" characters in XML.such as `>`, which are escaped to strings like `>`. It's the presentation layer that needs to correctly interpret those strings. You can find a lot of the [here](https://www.rapidtables.com/web/html/html-codes.html). – Thom A Jan 01 '20 at 16:52
  • Thank you. In this case, the presentation is a text file output ending with a .xfdl extension for a Lotus Notes viewer. The Lotus Notes viewer can interpret my data correctly when I do the replacements, but not the raw output that I'm getting from SQL. If I find the character as they are stored in the SQL table and replace them in my .xfdl files, then Lotus Notes can read them properly. That HTML character conversion chart is very helpful, but it doesn't cover some of the odd conversions like ('½', "½"). Is there a conversion table that might cover those translations as well? Thank you! – Chris Hansen Jan 01 '20 at 17:04
  • That seems like a code page issue when you imported/exported the data. – Thom A Jan 01 '20 at 17:09
  • Interesting, that sounds correct. It doesn't look like invoke-sqlcmd allows you to set a codepage though. I just found this article [link] (https://dba.stackexchange.com/questions/40516/sqlcmd-command-is-not-able-to-insert-accents) that discusses setting a code page when using sqlcmd, but I can't find anything similar for invoke-sqlcmd. Do you think it's worth abandoning invoke-sqlcmd and query sql in a different way that would allow me to set a code page? – Chris Hansen Jan 01 '20 at 17:17

0 Answers0