We are hitting a problem with the extremely useful FOR JSON RAW option:

CREATE TABLE Blah67(PK int default autoincrement, stuff char(50), primary key (PK));

INSERT INTO Blah67(Stuff) values ('£'); INSERT INTO Blah67(Stuff) values ('€'); INSERT INTO Blah67(Stuff) values ('é');

If I select the contents of the table as XML
select * from Blah67 for xml raw;
everything is fine: forxml(1,'PK',xmltab."1",'stuff',xmltab."2") ==================================================================== <row PK="1" stuff="£"/><row PK="2" stuff="€"/><row PK="3" stuff="é"/>

but with JSON

select * from Blah67 for JSON raw;
it all goes haywire: forjson(256,'PK',jsontab."1",'stuff',jsontab."2") ==================================================================== [{"PK":1,"stuff":"£"},{"PK":2,"stuff":"€"},{"PK":3,"stuff":"é"}]

reproducible in &

I am using catalog collation 1252LATIN1 and character set windows-1252

asked 20 Dec '18, 10:51

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

edited 20 Dec '18, 11:12


FWIW, if you change the Stuff column to NCHAR(50), the behaviour is identical. I had suspected it might make a difference.

"SELECT EXPRTYPE(...)" returns XML for both queries. The XML data type is CHAR-based, so that may be relevant here. JSON itself uses Unicode strings.

Resume: I don't have a clue.

(20 Dec '18, 11:34) Volker Barth

That is a bit odd. I can see that, internally, FOR JSON always builds its strings as NCHAR since JSON is always in unicode according to rfc34627.

But the server seems to describe FOR JSON values as the "XML" datatype which seems to be equivalent to "LONG VARCHAR". That would mean that the UTF-8 string would be treated as if it were CHAR charset and, assuming your terminal is using 1252LATIN1, it would not undergo character set translation on the way to the client. Unfortunately, I'm not very familiar with our XML datatype handling but, given that you appear to be getting a UTF-8 encoded string back (a string that should have been converted back to 1252LATIN1), we do appear to be treating FOR JSON as CHAR which is incorrect.

In the end, you want your JSON to be in NCHAR (UTF-8) since valid JSON must always be encoded in Unicode according to the standard.

Given what I see, I think the only workaround to get truly valid JSON is to use a database with a CHAR encoding of UTF-8. And I will need to report a bug...

permanent link

answered 20 Dec '18, 12:45

John%20Smirnios's gravatar image

John Smirnios
accept rate: 38%

Many thanks for that John - will look forward to a fix in due course. We'll try the workaround.

(20 Dec '18, 13:42) Justin Willey

FWIW, here's an older answer from Nick on the pecularties of XML being implemented as LONG VARCHAR...

(21 Dec '18, 03:18) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 20 Dec '18, 10:51

question was seen: 447 times

last updated: 21 Dec '18, 03:18