SqlServer converts query results to XML, JSON, and sqlserverjson

SqlServer converts query results to XML, JSON, and sqlserverjson

I used it a long time ago. Now I have compiled some code from the Internet, but some bugs have been modified by me.

1. Convert query results to XML

DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT * FROM table';DECLARE @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))SELECT @XML;SELECT @XMLString;

@ ParameterSQL is the statement to be queried, @ XMLXML format data, and @ XMLStringXML is converted into a string.

2. Convert the query result to JSON

Two steps are required to convert the query result to json. First, the query result is converted to XML data, and then converted to json data through XML data.

The XML-to-JSON stored procedure is as follows:

Create procedure [dbo]. [SerializeJSON] (@ XML, @ json_xml NVARCHAR (MAX) OUTPUT) asbegin declare @ XMLString NVARCHAR (MAX); SET @ XMLString = CAST (@ xml as nvarchar (MAX )); begin try -- // start capturing exceptions DECLARE @ json nvarchar (MAX); DECLARE @ Row VARCHAR (MAX); DECLARE @ RowStart INT; DECLARE @ RowEnd INT; DECLARE @ FieldStart INT; DECLARE @ FieldEnd INT; DECLARE @ key varchar (MAX); DECLARE @ Value VARCHAR (MAX); DECLARE @ StartRoot VARCHAR (100); SET @ StartRoot = '<row> '; DECLARE @ EndRoot VARCHAR (100); SET @ EndRoot = '</row>'; DECLARE @ StartField VARCHAR (100); SET @ StartField = '<'; DECLARE @ EndField VARCHAR (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 + '{'; -- // for each row SET @ FieldStart = CHARINDEX (@ StartField, @ Row, 0 ); WHILE @ FieldStart> 0 BEGIN -- // parse node key SET @ FieldStart = @ FieldStart + LEN (@ StartField); SET @ FieldEnd = CHARINDEX (@ EndField, @ Row, @ FieldStart); DECLARE @ end INT = CHARINDEX ('/>', @ Row, @ FieldStart ); IF @ end <1 OR @ end> @ FieldEnd begin set @ KEY = SUBSTRING (@ Row, @ FieldStart, @ FieldEnd-@ FieldStart ); SET @ JSON = @ JSON + '"' + @ KEY + '":'; -- // parse node value 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; if len (@ JSON)> 0 SET @ JSON = SUBSTRING (@ JSON, 0, LEN (@ JSON); SET @ JSON = @ JSON + '},'; -- // for each row SET @ RowStart = CHARINDEX (@ StartRoot, @ XMLString, @ RowEnd); END; if len (@ JSON)> 0 SET @ JSON = SUBSTRING (@ JSON, 0, LEN (@ JSON); -- // SET @ JSON = '[' + @ JSON + ']'; SET @ json_xml = @ JSON; end try -- // end catch exception begin catch -- // if an exception is caught, SET @ json_xml = @ XMLString; end catch; -- // END Exception Handling END;

The above section describes how SqlServer converts the query results to XML and JSON. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!