MSSQL: TO XML clause for ASCII format

[Jonas Sell]

Hi All,

I could need some help with creating an XML file from a Microsoft Server table.

While I understand that this should be a piece of cake using the TO XML clause in the query, I'd like the resulting .xml to be formatted in ASCII (the table contains Unicode nvarchar columns).
Is there any mode for the TO XML clause that lets you define the charset?

(why do I want to do this? I'm supposed to check if this it takes significantly longer than creating unicode .xml)

Alternatively, is there any way to to so using Hibernate? because that's how I filled the table in the first place

I know this topic isn't really JDBC-related, but to me it seemed most fitting to post it here.

TY in advance! Jonas

[Rob Spoor]

Welcome to the Ranch!

What are you going to do if the table contains Unicode characters that cannot be displayed in ASCII? ASCII is very limited in the characters it can handle.

[Jonas Sell]

Thank you

You're right, there's no real sense in reformatting to ASCII if you've got it in Unicode.
In my scenario, the data is designated for a system that can't handle Unicode. But I still want my db to contain unicode - unless the performance would suffer signficantly because of the transformation.

If I get occasional question marks or squares, that's cool.

[Jonas Sell]

I'm still looking for a solution that works with TO XML but if you guys think there's none, I'll have to find a workaround - so what do you think?

[Rob Spoor]

I've never used TO XML, so that's why I haven't responded. I noticed one possible issue, addressed it, and that was that

[Paul Clapham]

The usual way to produce XML for those unfortunate users who can only handle ASCII is to do an identity transformation on the XML document and use US-ASCII as the charset of the output. This will cause the transformed XML to contain character escapes for the non-ASCII characters. However this assumes that the system which is going to process that transformed XML will handle the character escapes in some reasonable way. But the only reasonable way would be for the XML parser on that Unicode-ignorant system to convert them back into the characters they represent, which the Unicode-ignorant system would then not be able to handle.

Bottom line (in my opinion): systems which can only handle ASCII shouldn't be given XML. So I guess I'm rejecting your requirement for ASCII-only XML. But anyway my solutions would require an extra transformation step, which it sounds like you're not keen on doing anyway.