This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MSSQL: TO XML clause for ASCII format

 
Jonas Sell
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20495
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20495
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20750
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic