Create JSON message in T-sql

• kaladharreddy15

  SSC-Addicted

  Points: 476

  More actions

  December 21, 2018 at 4:30 pm

  #381040

  Hi All,
     I need help to create a Json message using T-sql , If any 
  Here is my Requirement

  Table fields

  ID, Name, Class, marks 

  I need to generate JSON message for each id like below
  Id in one column and Json message (with name, class and marks) in one column 
  ID:1     jsonmsg
  "
      "name":"XXXX",
      "Data":[
      {
       "Class":"Clssa1",
       "Marks":"212312"
      }
     ],

  ID:2      Jsonmsg

  "
      "name":"YYYYY",
      "Data":[ 
      { 
       "Class":"Class1",
       "Marks":"54646346"
      }
     ],

  Please help me out on this.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 21, 2018 at 6:43 pm

  #2017148

  Without the base data that is being converted to a JSON message, not much can be offered.

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 22, 2018 at 1:11 am

  #2017153

  This might help you getting started
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @SAMPLE_DATA TABLE
(
  ID  INT   NOT NULL
 ,[NAME] VARCHAR(30) NOT NULL
 ,[CLASS] VARCHAR(10) NOT NULL
 ,[MARKS] VARCHAR(10) NOT NULL
);
INSERT INTO @SAMPLE_DATA(ID,[NAME],[CLASS],[MARKS])
VALUES
(1,'Xxxx','Class1','12345')
,(2,'Yyyy','Class2','54321')
;

  SELECT
  SD.[NAME] AS [Name]
 ,SD.CLASS AS [Data.Class]
 ,SD.MARKS AS [Data.Marks]
FROM  @SAMPLE_DATA  SD
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER
;


  Output
  
{"Name":"Xxxx","Data":{"Class":"Class1","Marks":"12345"}}
,{"Name":"Yyyy","Data":{"Class":"Class2","Marks":"54321"}}