Another Visual Basic question - data fails XML parsing

[rsingh]

Can't you set the writer to encode in utf16?

[Moose423956]

Thankfully, the client has agreed to replacing the dodgy characters with valid ones. Even I can manage to code that!

[BigRed]

That's an avenue I'm exploring via a REPLACE, but it's up to the client.




I believe this is what it's doing. I'm not a SQL Server expert, but I think the character set is "SQL_Latin1_General_CP1_CI_AS" - at least that's what I get when I run the command

SELECT DATABASEPROPERTYEX('DBName', 'Collation') DatabaseCollation;

Is that a character set?

It is, but collation can be specified down to column level. FWIW SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode
Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

XML in SQL Server? go use MongoDB, Postgress, nosql and leave us dbas alone

[DimPrawn]

Good idea. Do you know one?

Not on your rate, no.

[Moose423956]

Get a decent contractor in to sort it all out.

Good idea. Do you know one?

[DimPrawn]

Get a decent contractor in to sort it all out.

[Moose423956]

You could always remove the illegal characters.

That's an avenue I'm exploring via a REPLACE, but it's up to the client.

It says SqlException, is the data stored as XML in SQL Server, or does it write XML to SQL server and read the data from a file?

I believe this is what it's doing. I'm not a SQL Server expert, but I think the character set is "SQL_Latin1_General_CP1_CI_AS" - at least that's what I get when I run the command

SELECT DATABASEPROPERTYEX('DBName', 'Collation') DatabaseCollation;

Is that a character set?

Are you explicitly informing whatever does the parsing of what the encoding of the file is? I vaguely remember once having problems with MSXML under some circumstances if I forgot to tell it the input was UTF-8.

I'm struggling to find that in the code, but I have found a line that says

XmlTextWriter xmlTextWriter = new XmlTextWriter(stream, Encoding.UTF8);

so I assume that's where it's defined?

Apologies for my woolly answers, but I really am out of my depth.

[NickFitz]

Are you explicitly informing whatever does the parsing of what the encoding of the file is? I vaguely remember once having problems with MSXML under some circumstances if I forgot to tell it the input was UTF-8.

[DimPrawn]

Not enough information.

It says SqlException, is the data stored as XML in SQL Server, or does it write XML to SQL server and read the data from a file?

[VectraMan]

You could always remove the illegal characters.

Presumably it's some setting to do with character sets. You're meant to specify UTF-8 or whatever in the XML tag.