Format query result as json using FOR JSON and xml using FOR XML

Description : in this post sql query result return in json or xml by adding FOR JSON PATH or FOR XML PATH if need query result in json than add at last FOR JSON PATH and need result in XML than add at last FOR XML PATH and the result return in json or xml

JSON Return query syntex

SELECT TOP 3 sta_StateName AS StateName, sta_StateShortName AS ShortName FROM StateMaster FOR JSON PATH

JSON Result

[{"StateName":"Montana","ShortName":"MT"},{"StateName":"Maryland","ShortName":"MD"},{"StateName":"Tennessee","ShortName":"TN"}]

XML Return query syntex

SELECT TOP 3 sta_StateName AS StateName, sta_StateShortName AS ShortName FROM StateMaster FOR XML PATH

XML Result

<row><StateName>Montana</StateName><ShortName>MT</ShortName></row><row><StateName>Maryland</StateName><ShortName>MD</ShortName></row><row><StateName>Tennessee</StateName><ShortName>TN</ShortName></row>

How to use cursor in SQL

Description : In this post learn about Cursor. Cursor is difficult concept in SQL, cursor let you create a lop in your SQL procedure you can fatch data record by record. cursor stored data set that go through each record and than prform some kind of logic in record field

- Here is simple example of print state name and its short name

-- First declare State name and short name variables
DECLARE @sta_StateName Varchar(50), @sta_StateShortName Varchar(50)

-- Declare cursor name is state cursor that fatch 5 rows from State Master table
DECLARE StateCursor CURSOR FOR SELECT TOP 5 sta_StateName, sta_StateShortName FROM StateMaster

-- open the cursor
OPEN StateCursor;

-- here fetch 1st row from cursor
FETCH NEXT FROM StateCursor INTO @sta_StateName, @sta_StateShortName

-- while loop
WHILE @@FETCH_STATUS = 0
BEGIN
   
    -- print fetched state name
    PRINT @sta_StateName + ' ['+@sta_StateShortName+']'

    -- again fetch next row from cursor
    FETCH NEXT FROM StateCursor INTO @sta_StateName, @sta_StateShortName
END;

-- close the cursor
CLOSE StateCursor;

-- deallocate cursor
DEALLOCATE StateCursor;