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.

as I know sqlserver 2008 doesn't support json-features.
this thing was added from sqlserver 2016 and up.

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>