存储过程查询返回JSON序列导入到表（适用6.0.100json插件）

活字格的功能越来越强大，前两天发布了内测版6.0.100，增加了不少强大的功能，其中JSON功能感觉不错，所以就测试的时候顺便玩玩。之前使用存储过程返回的结果集一直使用XML的方式再解析到表，由于XML所生成的字符传过长，查询结果过多时还明显感觉到速度会有所减弱，相比JSON返回的序列就简洁得多。按理应该有所提升（个人理解，没有测试过），当然下面的方法也是从sqlserver查询中取得XML再拼接生成的JSON返回来的序列，这种做法在数据库处理的速度上大大优于页面的处理。

存储过程

1. -- =============================================
   
2. -- Author:                Lee lin
   
3. -- Create date: 2020-9-2 02:31:16
   
4. -- Description:        json_text
   
5. -- =============================================
   
6. CREATE PROCEDURE Pm_json_text
   
7. (@jsontext NVARCHAR(MAX) OUT )
   
8. 
   
9. AS
   
10. BEGIN
    
11.   
    
12. DECLARE @SQL NVARCHAR(MAX)
    
13. DECLARE @XMLString VARCHAR(MAX)
    
14. DECLARE @XML XML
    
15. DECLARE @Paramlist NVARCHAR(1000)
    
16. SET @Paramlist = N'@XML XML OUTPUT'
    
17. SET @SQL = 'WITH PrepareTable (XMLString)'
    
18. SET @SQL = @SQL + 'AS('
    
19. SET @SQL = @SQL + 'SELECT [A_A] AS AA,[B_A] AS BB,[C_A] AS CC FROM [dbo].[Table_JSON]'+ 'FOR XML RAW,TYPE,ELEMENTS'
    
20. SET @SQL = @SQL + ')'
    
21. SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
    
22. EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
    
23. SET @XMLString=CAST(@XML AS VARCHAR(MAX))
    
24.   
    
25. DECLARE @JSON VARCHAR(MAX)
    
26. DECLARE @Row VARCHAR(MAX)
    
27. DECLARE @RowStart INT
    
28. DECLARE @RowEnd INT
    
29. DECLARE @FieldStart INT
    
30. DECLARE @FieldEnd INT
    
31. DECLARE @KEY VARCHAR(MAX)
    
32. DECLARE @Value VARCHAR(MAX)
    
33.   
    
34. DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<row>'
    
35. DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</row>'
    
36. DECLARE @StartField VARCHAR(100);SET @StartField='<'
    
37. DECLARE @EndField VARCHAR(100);SET @EndField='>'
    
38.   
    
39. SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
    
40. SET @JSON=''
    
41. WHILE @RowStart>0
    
42. BEGIN
    
43.     SET @RowStart=@RowStart+Len(@StartRoot)
    
44.     SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
    
45.     SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
    
46.     SET @JSON=@JSON+'{'
    
47.   
    
48.     -- for each row
    
49.     SET @FieldStart=CharIndex(@StartField,@Row,0)
    
50.     WHILE @FieldStart>0
    
51.     BEGIN
    
52.         -- parse node key
    
53.         SET @FieldStart=@FieldStart+Len(@StartField)
    
54.         SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
    
55.         SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
    
56.         SET @JSON=@JSON+'"'+@KEY+'":'
    
57.         -- parse node value
    
58.         SET @FieldStart=@FieldEnd+1
    
59.         SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
    
60.         SET @Value=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
    
61.         SET @JSON=@JSON+'"'+@Value+'",'
    
62.   
    
63.         SET @FieldStart=@FieldStart+Len(@StartField)
    
64.         SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
    
65.         SET @FieldStart=CharIndex(@StartField,@Row,@FieldEnd)
    
66.     END   
    
67.     IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
    
68.     SET @JSON=@JSON+'},'
    
69.     --/ for each row
    
70.   
    
71.     SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
    
72. END
    
73. IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
    
74. SET @JSON='['+@JSON+']'
    
75. SELECT @jsontext=@JSON
    
76.   
    
77. END
    
78. GO

复制代码

设置存储过程


设置json导入命令


几步就完成。看效果


      Document.fgcc (158.83 KB, 下载次数: 215)

2020-9-2 03:42 上传

点击文件名下载附件           json.rar (89.69 KB, 下载次数: 201)

2020-9-2 03:47 上传

点击文件名下载附件

再给有兴趣的格友一个思路：

楼主的方法很好，不过还有一点点缺点，那就是要到数据库后台里面去写一个存储过程，
一旦这个储存过程需要修改，那也必须要连到数据库后台。如果客户的项目不允许我们远程维护，那就比较麻烦了。

那有没有方法可以在用sql语法，让活字格可以得到任意的返回值呢？答案一定是可以的。

思路：
1）建立一个表，2列：sql方案名称，sql代码
2）用c#做一个服务器api，可以接收用活字格post发过来的sql代码，作为参数，然后在api中执行sql，得到返回值后，再回传给活字格。（sql的返回值有两个格式json和xml，如果是新版sql，建议大家直接用json了，老版本的好像只支持xml）
3）得到回传值后，如果是json的，可以直接利用楼主的方式。如果是xml的方式，需要自己
写js解析。
最后再写个页面，可以直接修改这个sql方案表。这样就可以变成一个万能sql数据存取解决方案了。

sz_xd 发表于 2021-5-2 20:04
@dystar001 :   你这提供这思路是非常好及正确的 （发现你喜欢搞动态功能，这是一劳永逸做法），这是可以 ...

是的，这种方案尽量用动态来解决，第一次麻烦点，但是以后是100%复用的。
我们内部管这个方案叫"万能SQL“，不局限具体功能，只要传SQL代码就好了。

dystar001 发表于 2020-9-2 23:27
再给有兴趣的格友一个思路：

楼主的方法很好，不过还有一点点缺点，那就是要到数据库后台里面去写一个存 ...

@dystar001 :   你这提供这思路是非常好及正确的 （发现你喜欢搞动态功能，这是一劳永逸做法），这是可以实现的，用 服务器 Web API  中的C# DLL 生产 的动态代码 执行 动态SQL 再 返回值 或动态生成 Json  ，再将这值传回给活字格，经实践发现这也是非常快的，

dystar001 发表于 2020-9-2 23:27
再给有兴趣的格友一个思路：

楼主的方法很好，不过还有一点点缺点，那就是要到数据库后台里面去写一个存 ...

这个方案的API怎么写？能否按楼主的demo改写下发个出来？谢谢分享

咖啡里 发表于 2020-9-3 00:05
这个就是通用的存储过程。定义一个传入的参数，这个参数就是要查询表的SELECT拼接语句，只要期初把这个通 ...

抱歉，前面没有仔细看您的存储过程代码。
您这个存储过程，的确是只要把sql查询的这段语句分解出来，这个就可以变成一个万能的存取方案了。
建议您把demo改一下，加上sql数据参数部分。这样格友看上去会更理解。
否则一开始可能会认为，这个demo只是分解数据用的。

Simon.hu 发表于 2020-9-4 09:20
我李总每次都是这么无私奉献~

李总，我们等你的蓝牙打印哈~

这是要约吗

我李总每次都是这么无私奉献~

李总，我们等你的蓝牙打印哈~

咖啡里 发表于 2020-9-3 11:53
这个是sql2008的数据库完整备份，最低恢复到同一版本或更高版本即可！

非常感谢回复指教，谢谢！

是的，主要是以前主流的是xml现在主流的是json

遇见未知的自己 发表于 2020-9-3 11:55
SQL2106以下版本才用这个参数FOR XML，SQL2016以上的版本的可以直接使用FOR JOSN

是的，sql2016版本； SELECT [A_A] AS AA,[B_A] AS BB,[C_A] AS CC FROM [Table_JSON]  for json auto