July 20, 2010 at 3:09 pm
Hi,
I have a SQL 2005 query that produces XML output with the layout I need. The problem is it will be imported into another system that cannot accept extended characters like accented letters, em dash, etc. in the data. I wrote a function to convert the extended characters to &#nnn; but if I use it within the query the ampersand is encoded again by SQL server and I get & amp;#nnn;. If I use the function on the output it works but is much too slow operating on a multi megabyte input. Is there a way to get the XML out in a different encoding? I have tried bcp to a file with various code pages but no luck so far.
Thanks
July 21, 2010 at 8:32 am
Well it turns out that this can be made to work by using a conversion function within the XML producing query, converting the output to varchar(max), then using REPLACE to replace &a mp;# with &#. Not ideal but it gets there.
August 11, 2010 at 11:45 am
It sounds like your original query was treating your XML data as string data. There are a number of ways that you can correct this, but since you didn't post your original query, we can't really tell you the best way to correct it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply