SqlServer将查询结果转换为XML和JSON
很久之前用到的,现在整理在这,里面一些代码来源于网上,不过有些bug已被我修改了。
1.查询结果转XML
DECLARE@ParameterSQLNVARCHAR(MAX)='SELECT*FROMtable'; DECLARE@SQLNVARCHAR(MAX) DECLARE@XMLStringVARCHAR(MAX) DECLARE@XMLXML DECLARE@ParamlistNVARCHAR(1000) SET@Paramlist=N'@XMLXMLOUTPUT' SET@SQL='WITHPrepareTable(XMLString)' SET@SQL=@SQL+'AS(' SET@SQL=@SQL+@ParameterSQL+'FORXMLRAW,TYPE,ELEMENTS' SET@SQL=@SQL+')' SET@SQL=@SQL+'SELECT@XML=[XMLString]FROM[PrepareTable]' EXECsp_executesql@SQL,@Paramlist,@XML=@XMLOUTPUT SET@XMLString=CAST(@XMLASVARCHAR(MAX)) SELECT@XML; SELECT@XMLString;
其中@ParameterSQL为要查询的语句,@XMLXML格式数据,@XMLStringXML转成字符串
2.查询结果转JSON
查询结果转换成json需要经过两个步骤,首先将查询结果转成XML数据,然后通过XML数据转成json
XML转JSON的存储过程如下:
CREATEPROCEDURE[dbo].[SerializeJSON]( @XMLXML, @json_xmlNVARCHAR(MAX)OUTPUT ) AS BEGIN DECLARE@XMLStringNVARCHAR(MAX); SET@XMLString=CAST(@XMLASNVARCHAR(MAX)); BEGINTRY--//开始捕捉异常 DECLARE@JSONNVARCHAR(MAX); DECLARE@RowVARCHAR(MAX); DECLARE@RowStartINT; DECLARE@RowEndINT; DECLARE@FieldStartINT; DECLARE@FieldEndINT; DECLARE@KEYVARCHAR(MAX); DECLARE@ValueVARCHAR(MAX); DECLARE@StartRootVARCHAR(100); SET@StartRoot=''; DECLARE@EndRootVARCHAR(100); SET@EndRoot='
'; DECLARE@StartFieldVARCHAR(100); SET@StartField='<'; DECLARE@EndFieldVARCHAR(100); SET@EndField='>'; SET@RowStart=CHARINDEX(@StartRoot,@XMLString,0); SET@JSON=''; WHILE@RowStart>0 BEGIN SET@RowStart=@RowStart+LEN(@StartRoot); SET@RowEnd=CHARINDEX(@EndRoot,@XMLString,@RowStart); SET@Row=SUBSTRING(@XMLString,@RowStart,@RowEnd-@RowStart); SET@JSON=@JSON+'{'; --//foreachrow SET@FieldStart=CHARINDEX(@StartField,@Row,0); WHILE@FieldStart>0 BEGIN --//parsenodekey SET@FieldStart=@FieldStart+LEN(@StartField); SET@FieldEnd=CHARINDEX(@EndField,@Row,@FieldStart); DECLARE@endINT=CHARINDEX('/>',@Row,@FieldStart); IF@end<1 OR@end>@FieldEnd BEGIN SET@KEY=SUBSTRING(@Row,@FieldStart,@FieldEnd-@FieldStart); SET@JSON=@JSON+'"'+@KEY+'":'; --//parsenodevalue SET@FieldStart=@FieldEnd+1; SET@FieldEnd=CHARINDEX('',@Row,@FieldStart); SET@Value=SUBSTRING(@Row,@FieldStart,@FieldEnd-@FieldStart); SET@JSON=@JSON+'"'+@Value+'",'; END; SET@FieldStart=@FieldStart+LEN(@StartField); SET@FieldEnd=CHARINDEX(@EndField,@Row,@FieldStart); SET@FieldStart=CHARINDEX(@StartField,@Row,@FieldEnd); END; IFLEN(@JSON)>0 SET@JSON=SUBSTRING(@JSON,0,LEN(@JSON)); SET@JSON=@JSON+'},'; --//foreachrow SET@RowStart=CHARINDEX(@StartRoot,@XMLString,@RowEnd); END; IFLEN(@JSON)>0 SET@JSON=SUBSTRING(@JSON,0,LEN(@JSON)); --//SET@JSON='['+@JSON+']'; SET@json_xml=@JSON; ENDTRY--//结束捕捉异常 BEGINCATCH--//有异常被捕获 SET@json_xml=@XMLString; ENDCATCH;--//结束异常处理 END;
以上所述是小编给大家介绍的SqlServer将查询结果转换为XML和JSON,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!