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

This article will explain in detail how to convert query results into XML and JSON in SqlServer. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. The query result is transferred 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.

two。 The query result is transferred to JSON

It takes two steps to convert the query result to json. First, the query result is converted into XML data, and then the XML data is converted into json.

The stored procedure for converting XML to JSON is as follows:

CREATE PROCEDURE [dbo]. [SerializeJSON] (@ XML XML, @ json_xml NVARCHAR (MAX) OUTPUT) ASBEGIN DECLARE @ XMLString NVARCHAR (MAX); SET @ XMLString = CAST (@ XML AS NVARCHAR (MAX)); BEGIN TRY--// starts catching 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; SET @ StartRoot ='; DECLARE @ EndRoot VARCHAR; SET @ EndRoot ='; DECLARE @ StartField VARCHAR; SET @ StartField =''; 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@FieldEnd BEGIN SET @ KEY = SUBSTRING (@ Row, @ FieldStart, @ FieldEnd-@ FieldStart); SET @ JSON = @ JSON +''+ @ KEY +':' -- / / parse node value SET @ FieldStart = @ FieldEnd + 1; SET @ 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.