自己练手写了一个将JSON数据输出成表值数据的存储过程
存储过程代码
1 CREATE PROC Proc_JsonConvertTable(@JSON VARCHAR(MAX)) 2 AS 3 --JSON测试数据 4 --SET @JSON='[{name:张三,age:18,hobby:打篮球},{name:李四,age:30,hobby:唱歌},{name:王五,age:33,hobby:跳舞}]'; 5 --JSON测试数据处理 6 SET @JSON=REPLACE(@JSON,'[',''); 7 SET @JSON=REPLACE(@JSON,']',''); 8 SET @JSON=REPLACE(@JSON,'},{','}-*{'); 9 DECLARE @ColName VARCHAR(15), 10 @ColName_CN VARCHAR(15), 11 @Value VARCHAR(100); 12 13 --取列名 14 SELECT TOP 1 15 @Value=Value 16 FROM dbo.SplitString(@JSON,'-*',1); 17 DECLARE @Value2 VARCHAR(100); 18 SET @Value2=@Value; 19 SET @Value2=REPLACE(@Value2,':',','); 20 SET @Value2=REPLACE(@Value2,'"',''); 21 SET @Value2=REPLACE(@Value2,'{',''); 22 SET @Value2=REPLACE(@Value2,'}',''); 23 DECLARE cr1 CURSOR 24 FOR 25 SELECT Value 26 FROM dbo.SplitString(@Value2,',',1); 27 OPEN cr1; 28 DECLARE @col VARCHAR(50), 29 @createSQL VARCHAR(500); 30 --拼接创建临时表的SQL 31 SET @createSQL='CREATE TABLE #TABLE ('; 32 FETCH NEXT FROM cr1 INTO @col; 33 34 DECLARE @forindex INT; 35 SET @forindex=2; 36 WHILE @@FETCH_STATUS=0 37 BEGIN 38 IF @forindex%2=0 39 BEGIN 40 SET @createSQL=@createSQL+@col+' VARCHAR(50) NOT NULL,'; 41 SET @JSON=REPLACE(@JSON,'"',''); 42 SET @JSON=CONVERT(VARCHAR(500),REPLACE(@JSON,':','ACC')); 43 SET @JSON=REPLACE(@JSON,@col+'ACC',''); 44 END; 45 SET @forindex=@forindex+1; 46 FETCH NEXT FROM cr1 INTO @col; 47 48 END; 49 CLOSE cr1; 50 DEALLOCATE cr1; 51 SET @createSQL=SUBSTRING(@createSQL,0,LEN(@createSQL)); 52 SET @createSQL=@createSQL+');'; 53 PRINT @createSQL; 54 55 56 --处理JSON数据,并将数据插入到临时表 57 DECLARE cr CURSOR 58 FOR 59 SELECT Value 60 FROM dbo.SplitString(@JSON,'-*',1); 61 OPEN cr; 62 FETCH NEXT FROM cr INTO @Value; 63 64 WHILE @@FETCH_STATUS=0 65 BEGIN 66 SET @Value=REPLACE(@Value,'{',''); 67 SET @Value=REPLACE(@Value,'}',''); 68 SET @Value=REPLACE(@Value,'"',''); 69 70 PRINT @Value; 71 SET @col=(SELECT ''''+Value+''''+',' 72 FROM SplitString(@Value,',',1) 73 FOR XML PATH('')); 74 SELECT @col=SUBSTRING(@col,0,LEN(@col)); 75 PRINT @col; 76 SET @createSQL=@createSQL+'INSERT INTO #TABLE SELECT '+@col+';'; 77 FETCH NEXT FROM cr INTO @Value; 78 END; 79 SET @createSQL=@createSQL+'SELECT * FROM #TABLE;'; 80 PRINT @createSQL; 81 CLOSE cr; 82 DEALLOCATE cr; 83 84 --执行SQL,并输出结果 85 EXEC(@createSQL);测试存储过程
EXEC Proc_JsonConvertTable @JSON='[{name:蔡徐坤,age:18,hobby:唱、跳、Rap、篮球},{name:李四,age:30,hobby:唱歌},{name:王五,age:33,hobby:跳舞}]'
测试结果
转载于:https://www.cnblogs.com/daiwk/p/10791960.html
相关资源：数据结构—成绩单生成器