SqlServer 2005升级至SqlServer 2008 解析Json 字符集问题

            IF
            OBJECT_ID (N‘dbo.parseJSON‘) ISNOTNULLDROPFUNCTION dbo.parseJSON

GOIFEXISTS (SELECT*FROM sys.types WHERE name LIKE‘Hierarchy‘)
  DROP TYPE dbo.Hierarchy
goCREATE TYPE dbo.Hierarchy ASTABLE/*Markup languages such as JSON and XML all represent object data as hierarchies. Although it looks very different to the entity-relational model, it isn‘t. It is rather more a different perspective on the same model. The first trick is to represent it as a Adjacency list hierarchy in a table, and then use the contents of this table to update the database. This Adjacency list is really the Database equivalent of any of the nested data structures that are used for the interchange of serialized information with the application, and can be used to create XML, OSX Property lists, Python nested structures or YAML as easily as JSON.

Adjacency list tables have the same structure whatever the data in them. This means that you can define a single Table-Valued  Type and pass data structures around between stored procedures. However, they are best held at arms-length from the data, since they are not relational tables, but something more like the dreaded EAV (Entity-Attribute-Value) tables. Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE. You can, alternatively, convert the hierarchical table into XML and interrogate that with XQuery
*/
(
   element_id INTprimarykey, /* internal surrogate primary key gives the order of parsing and the list order */
   sequenceNo [int]NULL, /* the place in the sequence for the element */
   parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */Object_IDINT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
   NAME NVARCHAR(2000),/* the name of the object, null if it hasn‘t got one */
   StringValue NVARCHAR(MAX) NOTNULL,/*the string representation of the value of the element. */
   ValueType VARCHAR(10) NOTnull/* the declared type of the value represented as a string in StringValue*/
)
goCREATEFUNCTION dbo.parseJSON( @JSONNVARCHAR(MAX))
RETURNS@hierarchyTABLE
  (
   element_id INTIDENTITY(1, 1) NOTNULL, /* internal surrogate primary key gives the order of parsing and the list order */
   sequenceNo [int]NULL, /* the place in the sequence for the element */
   parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */Object_IDINT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
   NAME NVARCHAR(2000),/* the name of the object */
   StringValue NVARCHAR(MAX) NOTNULL,/*the string representation of the value of the element. */
   ValueType VARCHAR(10) NOTnull/* the declared type of the value represented as a string in StringValue*/
  )
ASBEGINDECLARE@FirstObjectINT, --the index of the first open bracket found in the JSON string@OpenDelimiterINT,--the index of the next open bracket found in the JSON string@NextOpenDelimiterINT,--the index of subsequent open bracket found in the JSON string@NextCloseDelimiterINT,--the index of subsequent close bracket found in the JSON string@TypeNVARCHAR(10),--whether it denotes an object or an array@NextCloseDelimiterCharCHAR(1),--either a ‘}‘ or a ‘]‘@ContentsNVARCHAR(MAX), --the unparsed contents of the bracketed expression@StartINT, --index of the start of the token that you are parsing@endINT,--index of the end of the token that you are parsing@paramINT,--the parameter at the end of the next Object/Array token@EndOfNameINT,--the index of the start of the parameter at end of Object/Array token@tokenNVARCHAR(200),--either a string or object@valueNVARCHAR(MAX), -- the value as a string@SequenceNoint, -- the sequence number within a list@nameNVARCHAR(200), --the name as a string@parent_IDINT,--the next parent ID to allocate@lenJSONINT,--the current length of the JSON String@charactersNCHAR(36),--used to convert hex to decimal@resultBIGINT,--the value of the hex symbol being parsed@indexSMALLINT,--used for parsing the hex value@EscapeINT--the index of the next escape characterDECLARE@StringsTABLE/* in this temporary table we keep all strings, even the names of the elements, since they are ‘escaped‘ in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
    (
     String_ID INTIDENTITY(1, 1),
     StringValue NVARCHAR(MAX)
    )
  SELECT--initialise the characters to convert hex to ascii@characters=‘0123456789abcdefghijklmnopqrstuvwxyz‘,
    @SequenceNo=0, --set the sequence no. to something sensible./* firstly we process all strings. This is done because [{} and ] aren‘t escaped in strings, which complicates an iterative parse. */@parent_ID=0;
  WHILE1=1--forever until there is nothing more to doBEGINSELECT@start=PATINDEX(‘%[^a-zA-Z]["]%‘, @json collate SQL_Latin1_General_CP850_Bin);--next delimited stringIF@start=0BREAK--no more so drop through the WHILE loopIFSUBSTRING(@json, @start+1, 1)=‘"‘BEGIN--Delimited NameSET@start=@Start+1;
          SET@end=PATINDEX(‘%[^\]["]%‘, RIGHT(@json, LEN(@json+‘|‘)-@start) collate SQL_Latin1_General_CP850_Bin);
        ENDIF@end=0--no end delimiter to last stringBREAK--no moreSELECT@token=SUBSTRING(@json, @start+1, @end-1)
      --now put in the escaped control charactersSELECT@token=REPLACE(@token, FROMString, TOString)
      FROM
        (SELECT‘\"‘AS FromString, ‘"‘AS ToString
         UNIONALLSELECT‘\\‘, ‘\‘UNIONALLSELECT‘\/‘, ‘/‘UNIONALLSELECT‘\b‘, CHAR(08)
         UNIONALLSELECT‘\f‘, CHAR(12)
         UNIONALLSELECT‘\n‘, CHAR(10)
         UNIONALLSELECT‘\r‘, CHAR(13)
         UNIONALLSELECT‘\t‘, CHAR(09)
        ) substitutions
      SELECT@result=0, @escape=1--Begin to take out any hex escape codesWHILE@escape>0BEGINSELECT@index=0,
          --find the next hex escape sequence@escape=PATINDEX(‘%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%‘, @token collate SQL_Latin1_General_CP850_Bin)
          IF@escape>0--if there is oneBEGINWHILE@index<4--there are always four digits to a \x sequence   BEGINSELECT--determine its value@result=@result+POWER(16, @index)
                    *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
                                @characters)-1), @index=@index+1 ;
         
                END-- and replace the hex sequence by its unicode valueSELECT@token=STUFF(@token, @escape, 6, NCHAR(@result))
            ENDEND--now store the string away INSERTINTO@Strings (StringValue) SELECT@token-- and replace the string with a tokenSELECT@JSON=STUFF(@json, @start, @end+1,
                    ‘@string‘+CONVERT(NVARCHAR(5), @@identity))
    END-- all strings are now removed. Now we find the first leaf.  WHILE1=1--forever until there is nothing more to doBEGINSELECT@parent_ID=@parent_ID+1--find the first object or list by looking for the open bracketSELECT@FirstObject=PATINDEX(‘%[{[[]%‘, @json collate SQL_Latin1_General_CP850_Bin)--object or arrayIF@FirstObject=0BREAKIF (SUBSTRING(@json, @FirstObject, 1)=‘{‘) 
    SELECT@NextCloseDelimiterChar=‘}‘, @type=‘object‘ELSESELECT@NextCloseDelimiterChar=‘]‘, @type=‘array‘SELECT@OpenDelimiter=@firstObjectWHILE1=1--find the innermost object or list...BEGINSELECT@lenJSON=LEN(@JSON+‘|‘)-1--find the matching close-delimiter proceeding after the open-delimiterSELECT@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
                                      @OpenDelimiter+1)
  --is there an intervening open-delimiter of either typeSELECT@NextOpenDelimiter=PATINDEX(‘%[{[[]%‘,
             RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--objectIF@NextOpenDelimiter=0BREAKSELECT@NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiterIF@NextCloseDelimiter<@NextOpenDelimiterBREAKIFSUBSTRING(@json, @NextOpenDelimiter, 1)=‘{‘SELECT@NextCloseDelimiterChar=‘}‘, @type=‘object‘ELSESELECT@NextCloseDelimiterChar=‘]‘, @type=‘array‘SELECT@OpenDelimiter=@NextOpenDelimiterEND---and parse out the list or name/value pairsSELECT@contents=SUBSTRING(@json, @OpenDelimiter+1,
                        @NextCloseDelimiter-@OpenDelimiter-1)
  SELECT@JSON=STUFF(@json, @OpenDelimiter,
                @NextCloseDelimiter-@OpenDelimiter+1,
                ‘@‘+@type+CONVERT(NVARCHAR(5), @parent_ID))
  WHILE (PATINDEX(‘%[A-Za-z0-9@+.e]%‘, @contents collate SQL_Latin1_General_CP850_Bin))<>0BEGINIF@Type=‘Object‘--it will be a 0-n list containing a string followed by a string, number,boolean, or nullBEGINSELECT@SequenceNo=0,@end=CHARINDEX(‘:‘, ‘‘+@contents)--if there is anything, it will be a string-based name.SELECT@start=PATINDEX(‘%[^A-Za-z@][@]%‘, ‘‘+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAASELECT@token=SUBSTRING(‘‘+@contents, @start+1, @End-@Start-1),
            @endofname=PATINDEX(‘%[0-9]%‘, @token collate SQL_Latin1_General_CP850_Bin),
            @param=RIGHT(@token, LEN(@token)-@endofname+1)
          SELECT@token=LEFT(@token, @endofname-1),
            @Contents=RIGHT(‘‘+@contents, LEN(‘‘+@contents+‘|‘)-@end-1)
          SELECT@name=stringvalue FROM@stringsWHERE string_id=@param--fetch the nameENDELSESELECT@Name=null,@SequenceNo=@SequenceNo+1SELECT@end=CHARINDEX(‘,‘, @contents)-- a string-token, object-token, list-token, number,boolean, or nullIF@end=0SELECT@end=PATINDEX(‘%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%‘, @Contents+‘‘ collate SQL_Latin1_General_CP850_Bin)
          +1SELECT@start=PATINDEX(‘%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%‘, ‘‘+@contents collate SQL_Latin1_General_CP850_Bin)
      --select @start,@end, LEN(@contents+‘|‘), @contents  SELECT@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
        @Contents=RIGHT(@contents+‘‘, LEN(@contents+‘|‘)-@end)
      IFSUBSTRING(@value, 1, 7)=‘@object‘INSERTINTO@hierarchy
          (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
          SELECT@name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
            SUBSTRING(@value, 8, 5), ‘object‘ELSEIFSUBSTRING(@value, 1, 6)=‘@array‘INSERTINTO@hierarchy
            (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
            SELECT@name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
              SUBSTRING(@value, 7, 5), ‘array‘ELSEIFSUBSTRING(@value, 1, 7)=‘@string‘INSERTINTO@hierarchy
              (NAME, SequenceNo, parent_ID, StringValue, ValueType)
              SELECT@name, @SequenceNo, @parent_ID, stringvalue, ‘string‘FROM@stringsWHERE string_id=SUBSTRING(@value, 8, 5)
          ELSEIF@valueIN (‘true‘, ‘false‘) 
              INSERTINTO@hierarchy
                (NAME, SequenceNo, parent_ID, StringValue, ValueType)
                SELECT@name, @SequenceNo, @parent_ID, @value, ‘boolean‘ELSEIF@value=‘null‘INSERTINTO@hierarchy
                  (NAME, SequenceNo, parent_ID, StringValue, ValueType)
                  SELECT@name, @SequenceNo, @parent_ID, @value, ‘null‘ELSEIFPATINDEX(‘%[^0-9]%‘, @value collate SQL_Latin1_General_CP850_Bin)>0INSERTINTO@hierarchy
                    (NAME, SequenceNo, parent_ID, StringValue, ValueType)
                    SELECT@name, @SequenceNo, @parent_ID, @value, ‘real‘ELSEINSERTINTO@hierarchy
                    (NAME, SequenceNo, parent_ID, StringValue, ValueType)
                    SELECT@name, @SequenceNo, @parent_ID, @value, ‘int‘if@Contents=‘‘Select@SequenceNo=0ENDENDINSERTINTO@hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
  SELECT‘-‘,1, NULL, ‘‘, @parent_id-1, @type--
RETURNENDGO

Msg 207, Level16, State 1, Procedure parseJSON, Line 65
列名 ‘FROMString‘ 无效。
Msg 207, Level16, State 1, Procedure parseJSON, Line 65
列名 ‘TOString‘ 无效。

            SELECT
            @token
            =
            REPLACE(@token, FromString, ToString)

            Msg 245, Level 16, State 1, Line 1
在将 nvarchar 值 ‘1:@st‘ 转换成数据类型 int 时失败。
        

            alter
            database TestDB2 collate Chinese_Taiwan_Stroke_CI_AS

Msg 5030, Level16, State 2, Line 1
无法用排他锁锁定该数据库，以执行该操作。
Msg 5072, Level16, State 1, Line 1ALTERDATABASE 失败。无法将数据库 ‘XXXXXX‘ 的默认排序规则设置为 Chinese_Taiwan_Stroke_CI_AS。

Msg 5075, Level16, State 1, Line 1
对象 ‘XXXXXX‘ 依赖于 数据库排序规则。如果绑定到架构的对象依赖于数据库排序规则，则无法更改数据库排序规则。请删除数据库排序规则的依赖项，然后重试操作。
Msg 5072, Level16, State 1, Line 1
....
ALTERDATABASE 失败。无法将数据库 ‘TestDB2‘ 的默认排序规则设置为 Chinese_Taiwan_Stroke_CI_AS。

            alter
            database TestDB2 collate Chinese_Taiwan_Stroke_CI_AS