If your SQL server version is 2017 or higher, try the function STRING_AGG():
Since the datatype of [text] is nvarchar(max), I added N before the separator ' ':
SELECT STRING_AGG([text], N' ') AS jsonCol
FROM [dbo].[test];
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have a table with one column where json data are exported from a external data source.
How do I convert this josn into SQL server table.
Please find the table script below
CREATE TABLE [dbo].[test](
[text] nvarchar NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[test] ([text]) VALUES (N'[')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151952+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151952",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 99,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151944+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151944",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 97,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151940+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151940",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 95,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151936+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-09-14",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151936",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 93,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' },')
GO
INSERT [dbo].[test] ([text]) VALUES (N' {')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "id": "VN110001+70151931+2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "StartingWeekAnchorDate": "2020-10-16",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "CustomerId": "70151931",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Frequency": 7,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Active": true,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "RouteId": "VN110001",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "ScheduleTypeId": "2",')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "Sequence": 91,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitMonday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitTuesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitWednesday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitThursday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitFriday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSaturday": false,')
GO
INSERT [dbo].[test] ([text]) VALUES (N' "VisitSunday": true')
GO
INSERT [dbo].[test] ([text]) VALUES (N' }')
GO
INSERT [dbo].[test] ([text]) VALUES (N']')
GO
If your SQL server version is 2017 or higher, try the function STRING_AGG():
Since the datatype of [text] is nvarchar(max), I added N before the separator ' ':
SELECT STRING_AGG([text], N' ') AS jsonCol
FROM [dbo].[test];
Thanks for your quick reply.
In this shared tables it is working perfectly.
But I have a table where more than 20000 rows are there.
The shared query not able to fetch all rows.
Can you please help me on this.
Please try the following approach for your real life table.
SQL
USE tempdb;
GO
DROP TABLE IF EXISTS dbo.test;
CREATE TABLE [dbo].[test](
[text] [nvarchar](max) NULL
)
INSERT [dbo].[test] ([text]) VALUES
(N'[')
, (N' {')
, (N' "id": "VN110001+70151952+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151952",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 99,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151944+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151944",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 97,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151940+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151940",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 95,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151936+2",')
, (N' "StartingWeekAnchorDate": "2020-09-14",')
, (N' "CustomerId": "70151936",')
, (N' "Frequency": 7,')
, (N' "Active": false,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 93,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' },')
, (N' {')
, (N' "id": "VN110001+70151931+2",')
, (N' "StartingWeekAnchorDate": "2020-10-16",')
, (N' "CustomerId": "70151931",')
, (N' "Frequency": 7,')
, (N' "Active": true,')
, (N' "RouteId": "VN110001",')
, (N' "ScheduleTypeId": "2",')
, (N' "Sequence": 91,')
, (N' "VisitMonday": false,')
, (N' "VisitTuesday": false,')
, (N' "VisitWednesday": false,')
, (N' "VisitThursday": false,')
, (N' "VisitFriday": false,')
, (N' "VisitSaturday": false,')
, (N' "VisitSunday": true')
, (N' }')
, (N']');
DECLARE @JSON NVARCHAR(MAX) = N'';
SELECT @JSON += text
FROM dbo.test;
SELECT @JSON;
-- UPDATE --
Another approach to use bcp.exe to generate JSON file out of this table.
The rest is trivial.
DECLARE @SQLCmd VARCHAR(8000)
, @outputFileName VARCHAR(256) = 'e:\Temp\output.json'
, @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
, @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT [text] FROM tempdb.dbo.test;'
, @tcpPort VARCHAR(10) = '1433'
, @WindowsAuth BIT = 1 -- 1 is for Windows Authentication, 0 is for SQL Server Authentication
, @loginID VARCHAR(100) = 'loginID'
, @password VARCHAR(100) = 'password'
, @serverName VARCHAR(100) = 'SPACESHIP';
-- /B "WindowTitle" parameters produce output in the SSSMS !!!
SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"'
+ ' "' + @SQL + '"'
+ ' queryout "' + @outputFileName + '"'
--+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
+ CASE WHEN @WindowsAuth = 1 THEN ' -T'
ELSE ' -U '+ @loginID + ' -P ' + @password
END
+ ' -c -C 65001 -a 32768'
+ ' -S "' + @serverName + ',' + @tcpPort + '"';
-- just to see it
SELECT @SQLCmd AS [Command to execute];
-- create file on the file system
EXECUTE master.sys.xp_cmdshell @SQLCmd;