Create JSON by Using SQL Server

Step 1:

Create stored procedure

Create PROCEDURE [dbo].[sp_GetJSONFromXML]

-- Add the parameters for the stored procedure here

@XMLData xml

AS

BEGIN

DECLARE @dataXML xml;

select @dataXML = @XMLData;

--select @Shopping;

SELECT Stuff(  

  (SELECT * from  

    (SELECT ',

    {'+  

      Stuff((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+

                    replace(b.c.value('text()[1]','NVARCHAR(MAX)'),'"','''') +'"'

               

             from x.a.nodes('*') b(c)  

             for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')

        ,1,1,'')+'}' 

   from @dataXML.nodes('/root/*') x(a)  

   ) JSON(theLine)  

  for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )

,1,1,'')

END

Step # 2:
Create a table and insert some records in it.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblNames](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NULL,

[City] [nvarchar](50) NULL,

 CONSTRAINT [PK_tblNames] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[tblNames] ON 

GO

INSERT [dbo].[tblNames] ([ID], [Name], [City]) VALUES (1, N'Rehan', N'Dubai')

GO

INSERT [dbo].[tblNames] ([ID], [Name], [City]) VALUES (2, N'Wasti', N'Dubai')

GO

INSERT [dbo].[tblNames] ([ID], [Name], [City]) VALUES (3, N'Imran', N'Dubai')

GO

INSERT [dbo].[tblNames] ([ID], [Name], [City]) VALUES (4, N'Salman', N'Abu Dhabi')

GO

INSERT [dbo].[tblNames] ([ID], [Name], [City]) VALUES (5, N'Kashif', N'Abu Dhani')

GO

INSERT [dbo].[tblNames] ([ID], [Name], [City]) VALUES (6, N'Asad', N'Sharjah')

GO

SET IDENTITY_INSERT [dbo].[tblNames] OFF

GO

Step # 3:

Execute SQL query:

Run these group of sql queries:

DECLARE @myXML xml;

select @myXML = (select * from tblnames FOR XML path, root)

exec sp_GetJSONFromXML @myXML;

You'll get Json Values:

Use this results in [ ]

Like:

 [{"ID":"1","Name":"Rehan","City":"Dubai"},

    {"ID":"2","Name":"Wasti","City":"Dubai"},

    {"ID":"3","Name":"Imran","City":"Dubai"},

    {"ID":"4","Name":"Salman","City":"Abu Dhabi"},

    {"ID":"5","Name":"Kashif","City":"Abu Dhani"},

    {"ID":"6","Name":"Asad","City":"Sharjah"}]