extended characters problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

extended characters problem

Author

Topic

LSTEINB
Starting Member

3 Posts

Posted - 2008-11-20 : 10:50:47

Hello all...I have a simple problem writing extended ASCII characters to an XML file - that I hope is simple to resolve! Here's an example of what I am doing:

I have a stored procedure called usp_ext_char that does the following:

PRINT '<?xml version="1.0" encoding="iso-8859-1" ?>'
PRINT '<dataroot>'
PRINT ' <ApplixIncident>'
PRINT ' <DealerName>APOLINÁRIOS-Maq. Agr. Ind. lda</DealerName>'
PRINT ' <Location>SANTARÉM</Location>'
PRINT ' </ApplixIncident>'
PRINT '</dataroot>'

When I run this procedure in Query Analyzer, all lines are printed as I expect, including the extended characters Á and É.

My problem comes when I try to write these lines to an xml file (call it ext_char.xml) I run the following command:

EXEC master..xp_cmdshell "isql /U uu /P pp /S ss /Q usp_ext_char /o c:\temp\ext_char.xml /n

When I open the file ext_char.xml, the contents look like this:

<?xml version="1.0" encoding="iso-8859-1" ?>
<dataroot>
<ApplixIncident>
<DealerName>APOLINARIOS-Maq. Agr. Ind. lda</DealerName>
<Location>SANTAR�M</Location>
</ApplixIncident>
</dataroot>

The extended characters Á and É do not display properly - no matter what I use to open it with (browser, notepad, XML Editor). I should note that if I "copy and paste" Á and É into the file and save, the characters I copied display fine when I open it.

Does any one know what I am missing or doing wrong? Thanks for any and all suggestions!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:54:24

try to save it within a CDATA section.

LSTEINB
Starting Member

3 Posts

Posted - 2008-11-20 : 13:36:28

Thanks for the suggestion...unfortunately it didn't work for me:

<?xml version="1.0" encoding="iso-8859-1" ?>
<dataroot>
<ApplixIncident>
<![CDATA[<DealerName>APOLINARIOS-Maq. Agr. Ind. lda</DealerName>]]>
<![CDATA[<Location>SANTAR�M</Location>]]>
</ApplixIncident>
</dataroot>

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-20 : 15:24:44

This is a problem with XP_CMDSHELL

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-20 : 15:36:01

Here is an example of not using xp_cmdshell.
It works:


INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=d:\',test#txt)
select char(193) as A
union
select char(201) as E

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-11-21 : 15:29:24

The problem is with the character encoding that isql uses to write its output file. It's not stated explicitly on the isql help, but if it uses the same default as its successor osql then it's code page 437 (generally referred to as "OEM" in Books Online for bad historical reasons).
Certainly the characters you're showing -- E acute being encoded as 0x90 and A acute getting degraded to an unaccented A is consistent with CP437.
http://en.wikipedia.org/wiki/Code_page_437

I don't think there's any way of setting the character encoding for output files from isql. If you are using SQL Server 2005 use sqlcmd instead and look at the -f flag.

LSTEINB
Starting Member

3 Posts

Posted - 2008-11-24 : 08:24:20

Thanks Arnold...unfortunately, we are still using SQL 2000, so it sounds like I am stuck for now. Thanks for the info...I will file away and revisit whenever we eventually upgrade.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources