Here are some simple methods to help you fix your SQL 9402 error.
In SQL Server, you must store XML with a column of type
XML . This old man has a lot of perks. It has a much higher level and implicit checks.
Based on my question, I am assuming you are storing your XML
NTEXT . This option has been deprecated for centuries and should not be supported in future alternatives! You must change that soon!
- 1-byte strings (
VARCHAR), or which in turn are extended ASCII
Important: this is far from UTF-8! Native UTF-8 support should be included in a future release.
- a group of 2 bytes (
NVARCHAR) which is definitely UTF-16 (UCS-2)
If XML has a great computer programming start-up declaration (in most cases it’s
utf-16 ), you can easily get it.
If the XML is stored as a double-byte string (at least that’s what
NTEXT tells my lifestyle), the declaration should always be
utf-16 . It must be a 1-byte string
This (and best of all, the simplest) is probably concludingThe motive was to completely omit the statement. Don’t you need it. Storing the new XML in the correct type will probably automatically result in this kill statement.
What to do: Create a new column, enter
XML , and concatenate all XML in that column. Be clear with any
NTEXT , and
IMAGE columns you may have!
Step into the future: be happy and enjoy this quick and easy ride with this native XML type 😀
UPDATE Environment Differences
([col1xml] to xml)
If your column most likely stores XML in its own type, you don’t have to repeat the casting every time (which is very expensive !!). But in the event that such a distribution depends on real XML. This, as stored in
NTEXT , is a 2-byte string. If all XML files begin with a declaration indicating an unsupported encoding (in most cases
utf-8 ), it will fail.
DECLARE @ xml2Byte_UTF16 NVARCHAR (100) = '
test1';SELECT AS Cast (@ xml2byte_utf16 XML);DECLARE @ xml1Byte_UTF8 VARCHAR (100) = ' test2';SELECT CAST (@ xml1Byte_UTF8 XML);
DECLARE vs. @ xml2Byte_UTF8 NVARCHAR (100) = '
test3';SELECT CAST (@ xml2Byte_UTF8 AS XML);DECLARE VARCHAR (100) = ' test4';SELECT CAST (@ xml1Byte_UTF16 AS XML);
[“Business Unit”: “Code”: “BU053”, “Label”: “Cloud and Data Platform”, “Product”: “Code”: “SSQRQ3”, “Label”: “IBM Cast Iron”, “ Component “:” “,” Platform “: [” code “:” PF025 “,” label “:” Platform independent “],” Version “:” All versions “,” Revision “:” “,” Line of business “: “code”: “”, “label”: “”]
Error: SQLSTATE:HY000 ERRORCODE: 9402 Error: XML parsing: line 1, character 38, not with encoding switching with Cast Iron
Technical Note (troubleshooting)
Passing XML history to MSSQL stored procedure encounters the following error: “SQLSTATE: HY000 ERRORCODE: 9402 Error message: [CastIron Systems] [JDBC SQLServer Driver] [SQLServer] XML Parsing: thirty eighth character on line 1 cannot change encoding “
This error occurs because MSSQL Web expects XML data to be UTF-16 encoded, but the default XML encoding is UTF-8.
Solve Your Problem
To resolve this issue, replace the entire encoding = UTF-8 tag in the report XML with encoding = UTF-16. After changing the design tag in the MSSQL XML document, you need to process the XML data.