查看: 283|回复: 0

[SQLServer] SqlServer将查询结果转换为XML和JSON

发表于 2017-10-8 08:00:01
尚学堂AD

很久之前用到的,现在整理在这,里面一些代码来源于网上,不过有些bug已被我修改了。

1.查询结果转XML

  1. DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT * FROM table';
  2. DECLARE @SQL NVARCHAR(MAX)
  3. DECLARE @XMLString VARCHAR(MAX)
  4. DECLARE @XML XML
  5. DECLARE @Paramlist NVARCHAR(1000)
  6. SET @Paramlist = N'@XML XML OUTPUT'
  7. SET @SQL = 'WITH PrepareTable (XMLString)'
  8. SET @SQL = @SQL + 'AS( '
  9. SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW,TYPE,ELEMENTS'
  10. SET @SQL = @SQL + ')'
  11. SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
  12. EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
  13. SET @XMLString=CAST(@XML AS VARCHAR(MAX))
  14. SELECT @XML;
  15. SELECT @XMLString;
复制代码

其中@ParameterSQL为要查询的语句,@XMLXML格式数据,@XMLStringXML转成字符串

2.查询结果转JSON

查询结果转换成json需要经过两个步骤,首先将查询结果转成XML数据,然后通过XML数据转成json

XML转JSON的存储过程如下:

  1. CREATE PROCEDURE [dbo].[SerializeJSON] (
  2. @XML XML,
  3. @json_xml NVARCHAR(MAX) OUTPUT
  4. )
  5. AS
  6. BEGIN
  7. DECLARE @XMLString NVARCHAR(MAX);
  8. SET @XMLString = CAST(@XML AS NVARCHAR(MAX));
  9. BEGIN TRY--//开始捕捉异常
  10. DECLARE @JSON NVARCHAR(MAX);
  11. DECLARE @Row VARCHAR(MAX);
  12. DECLARE @RowStart INT;
  13. DECLARE @RowEnd INT;
  14. DECLARE @FieldStart INT;
  15. DECLARE @FieldEnd INT;
  16. DECLARE @KEY VARCHAR(MAX);
  17. DECLARE @Value VARCHAR(MAX);
  18. DECLARE @StartRoot VARCHAR(100);
  19. SET @StartRoot = '<row>';
  20. DECLARE @EndRoot VARCHAR(100);
  21. SET @EndRoot = '</row>';
  22. DECLARE @StartField VARCHAR(100);
  23. SET @StartField = '<';
  24. DECLARE @EndField VARCHAR(100);
  25. SET @EndField = '>';
  26. SET @RowStart = CHARINDEX(@StartRoot, @XMLString, 0);
  27. SET @JSON = '';
  28. WHILE @RowStart>0
  29. BEGIN
  30. SET @RowStart = @RowStart + LEN(@StartRoot);
  31. SET @RowEnd = CHARINDEX(@EndRoot, @XMLString, @RowStart);
  32. SET @Row = SUBSTRING(@XMLString, @RowStart, @RowEnd - @RowStart);
  33. SET @JSON = @JSON + '{';
  34. --//for each row
  35. SET @FieldStart = CHARINDEX(@StartField, @Row, 0);
  36. WHILE @FieldStart>0
  37. BEGIN
  38. --//parse node key
  39. SET @FieldStart = @FieldStart + LEN(@StartField);
  40. SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart);
  41. DECLARE @end INT = CHARINDEX('/>', @Row, @FieldStart);
  42. IF @end<1
  43. OR @end>@FieldEnd
  44. BEGIN
  45. SET @KEY = SUBSTRING(@Row, @FieldStart, @FieldEnd - @FieldStart);
  46. SET @JSON = @JSON + '"' + @KEY + '":';
  47. --//parse node value
  48. SET @FieldStart = @FieldEnd + 1;
  49. SET @FieldEnd = CHARINDEX('</', @Row, @FieldStart);
  50. SET @Value = SUBSTRING(@Row, @FieldStart, @FieldEnd - @FieldStart);
  51. SET @JSON = @JSON + '"' + @Value + '",';
  52. END;
  53. SET @FieldStart = @FieldStart + LEN(@StartField);
  54. SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart);
  55. SET @FieldStart = CHARINDEX(@StartField, @Row, @FieldEnd);
  56. END;
  57. IF LEN(@JSON)>0
  58. SET @JSON = SUBSTRING(@JSON, 0, LEN(@JSON));
  59. SET @JSON = @JSON + '},';
  60. --// for each row
  61. SET @RowStart = CHARINDEX(@StartRoot, @XMLString, @RowEnd);
  62. END;
  63. IF LEN(@JSON)>0
  64. SET @JSON = SUBSTRING(@JSON, 0, LEN(@JSON));
  65. --//SET @JSON = '[' + @JSON + ']';
  66. SET @json_xml = @JSON;
  67. END TRY--//结束捕捉异常
  68. BEGIN CATCH--//有异常被捕获
  69. SET @json_xml = @XMLString;
  70. END CATCH;--//结束异常处理
  71. END;
复制代码

以上所述是小编给大家介绍的SqlServer将查询结果转换为XML和JSON,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对程序员之家网站的支持!



回复

使用道具 举报