-
Jul 31st, 2021, 10:09 AM
#1
Thread Starter
Member
Export Data to JSON from Database
I am using MSSQL 2008 and i need help exporting data from the database to a JSON file. The following is the code i have used to store the JSON file to the MSSQL 2008 Database.
Code:
SELECT *
FROM OPENROWSET (BULK 'C:\FilesTest\Dump\sample1.JSON', SINGLE_CLOB) as j
The code above saves the JSON data in the database table. Now i need the code that can export that data back to a JSON file, in a specified path when i call it. The JSON file should look like this :
Code:
{
"status": true,
"detail": "Verification Successful",
"data": {
"Number": "000001900019",
"Batchnumber": "677899 444404",
"Linked": "9",
"FullName": "Tuoyo Dante",
"Gender": "Male",
"LinkedEmail": null,
"PerformanceSummary": [
{
"BALANCE": "0",
"AMOUNT": "EUR 0",
"OVERDUE_AMOUNT": "0"
},
{
"BALANCE": "0",
"AMOUNT": "EUR 2,000",
"OVERDUE_AMOUNT": "0"
},
{
"BALANCE": "8,256",
"AMOUNT": "EUR 6,350,020",
"OVERDUE_AMOUNT": "0"
}
]
}
}
Kindly help. Been on this for days now.
-
Aug 6th, 2021, 05:57 PM
#2
Addicted Member
Re: Export Data to JSON from Database
as I know sqlserver 2008 doesn't support json-features.
this thing was added from sqlserver 2016 and up.
Ten Years After - 01 You Give Me Loving
-
Aug 7th, 2021, 03:22 AM
#3
Re: Export Data to JSON from Database
Here is a stored procedure which can be used to save a BLOB (containing JSON) to a text file
Code:
IF OBJECT_ID('usp_sys_SaveTextToFile') IS NOT NULL DROP PROC usp_sys_SaveTextToFile
GO
/*
DECLARE @Result INT
EXEC @Result = dbo.usp_sys_SaveTextToFile 'this is a test', 'C:\TEMP\test.txt' -- , @NoRaiseError=1
SELECT @Result AS Result
*/
CREATE PROC usp_sys_SaveTextToFile(
@Text NVARCHAR(MAX)
, @FileName VARCHAR(255)
, @Charset VARCHAR(50) = 'UTF-8' -- or 'Windows-1251'
, @NoRaiseError BIT = 0
) AS
SET NOCOUNT ON
DECLARE @RetVal INT
, @HObj INT
, @HResult INT
, @ErrSource VARCHAR(255)
, @ErrDescription VARCHAR(1000)
SELECT @RetVal = 0
BEGIN TRY
EXEC @HResult = dbo.sp_OACreate 'ADODB.Stream', @HObj OUTPUT
IF @HResult <> 0 GOTO QH
EXEC @HResult = dbo.sp_OAMethod @HObj, 'Open'
IF @HResult <> 0 GOTO QH
EXEC @HResult = dbo.sp_OASetProperty @HObj, 'Type', 2 -- adTypeText
IF @HResult <> 0 GOTO QH
IF @Charset IS NOT NULL
BEGIN
EXEC @HResult = dbo.sp_OASetProperty @HObj, 'Charset', @Charset
IF @HResult <> 0 GOTO QH
END
EXEC @HResult = dbo.sp_OAMethod @HObj, 'WriteText', NULL, @Text
IF @HResult <> 0 GOTO QH
EXEC @HResult = dbo.sp_OAMethod @HObj, 'SaveToFile', NULL, @FileName, 2 -- adSaveCreateOverWrite
IF @HResult <> 0 GOTO QH
EXEC @HResult = dbo.sp_OAMethod @HObj, 'Close'
IF @HResult <> 0 GOTO QH
END TRY
BEGIN CATCH
SELECT @RetVal = 1
IF COALESCE(@NoRaiseError, 0) = 0
BEGIN
SELECT @ErrSource = CONVERT(VARCHAR(50), ERROR_LINE())
, @ErrDescription = RTRIM(REPLACE(ERROR_MESSAGE(), CHAR(13) + CHAR(10), ' '))
RAISERROR ('Error on line %s: %s', 16, 1, @ErrSource, @ErrDescription)
END
END CATCH
QH:
IF @HResult <> 0
BEGIN
SELECT @RetVal = 1
IF COALESCE(@NoRaiseError, 0) = 0
BEGIN
EXEC dbo.sp_OAGetErrorInfo @HObj, @ErrSource OUTPUT, @ErrDescription OUTPUT
SELECT @ErrDescription = RTRIM(REPLACE(@ErrDescription, CHAR(13) + CHAR(10), ' '))
RAISERROR ('Error in %s: %s (0x%08X)', 16, 1, @ErrSource, @ErrDescription, @HResult)
END
END
IF @HObj IS NOT NULL
BEGIN
EXEC dbo.sp_OADestroy @HObj
SET @HObj = NULL
END
RETURN @RetVal
GO
This uses ADODB.Stream instances so you have to first enable OLE Automation in your SQL2008 configuration.
cheers,
</wqw>
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|