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>