Reply to: 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.

[Moose423956]

I'm still waiting to come up on the Premium Bonds. Until then, this is my lot!

[Einstein Jnr]

This is why I quit programming....

[Moose423956]

Have you tried looking at your xml line 16, character 23?

I can't, I'm guessing that's the position in the output file, but because the program crashes it doesn't create the output file so I can't check it. I'm certain it's one of the accented characters.

[Einstein Jnr]

Have you tried looking at your xml line 16, character 23?

[Einstein Jnr]

Are you still going through my code - first question is free, second incurs a fee