Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MSSQL: TO XML clause for ASCII format

 
Jonas Sell
Greenhorn
Posts: 3
  • 0
  • 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: 20388
46
Chrome Eclipse IDE Java Windows
  • 0
  • 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
  • 0
  • 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
  • 0
  • 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: 20388
46
Chrome Eclipse IDE Java Windows
  • 0
  • 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: 20196
26
MySQL Database
  • 0
  • 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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic