Results 1 to 3 of 3

Thread: Export Data to JSON from Database

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    56

    Thumbs up 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.

  2. #2
    Addicted Member sergeos's Avatar
    Join Date
    Apr 2009
    Location
    Belarus
    Posts
    162

    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

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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
  •  



Click Here to Expand Forum to Full Width