Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to convert query results into Json format in SQL Server". The explanation in this article is simple and clear and easy to learn and understand. Please follow Xiaobian's train of thought to study and learn "how to convert query results into Json format in SQL Server".

The copy code is as follows:

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [DBO]. [ParameterSQL AS VARCHAR (MAX)) ASBEGINDECLARE @ SQL NVARCHAR (MAX) DECLARE @ XMLString VARCHAR (MAX) DECLARE @ XML XMLDECLARE @ Paramlist NVARCHAR (1000) SET @ Paramlist = N'@XML XML OUTPUT'SET @ SQL = 'WITH PrepareTable (XMLString)' SET @ SQL = @ SQL+'AS ('SET @ SQL = @ SQL+ @ ParameterSQL+' FOR XML RAW,TYPE ELEMENTS'SET @ SQL = @ SQL +') 'SET @ SQL = @ SQL +' SELECT @ XML= [XMLString] From [PrepareTable] 'EXEC sp_executesql @ SQL, @ Paramlist, @ XML=@XML OUTPUTSET @ XMLString=CAST (@ XML AS VARCHAR (MAX)) DECLARE @ JSON VARCHAR (MAX) DECLARE @ Row VARCHAR (MAX) DECLARE @ RowStart INTDECLARE @ RowEnd INTDECLARE @ FieldStart INTDECLARE @ KEY VARCHAR (MAX) DECLARE @ Value VARCHAR (MAX) DECLARE @ StartRoot VARCHAR SET @ StartRoot=''DECLARE @ EndRoot VARCHAR; SET @ EndRoot=''DECLARE @ StartField VARCHAR SET @ StartField=''SET @ RowStart=CharIndex (@ StartRoot,@XMLString,0) SET @ JSON=''WHILE @ RowStart > 0BEGINSET @ RowStart=@RowStart+Len (@ StartRoot) SET @ RowEnd=CharIndex (@ EndRoot,@XMLString,@RowStart) SET @ Row=SubString (@ XMLString,@RowStart,@RowEnd-@RowStart) SET @ JSON=@JSON+' {'--for each rowSET @ FieldStart=CharIndex (@ StartField,@Row,0) WHILE @ FieldStart > 0BEGINColi-parse node keySET @ FieldStart=@FieldStart+Len (@ StartField) SET @ FieldEnd=CharIndex (@ EndField,@Row,@FieldStart) SET @ KEY=SubString (@ Row,@FieldStart) FieldEnd-@FieldStart) SET @ JSON=@JSON+' "'+ @ KEY+'":'--parse node valueSET @ FieldStart=@FieldEnd+1SET @ FieldEnd=CharIndex ('

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.