We help IT Professionals succeed at work.

2012 SQL to JSON Select

Aleks
Aleks asked
on
1,049 Views
Last Modified: 2017-04-08
I am using MS SQL 2012. Is there a way to get results in a JSON format from a recordset and use them to pass them as a string to post to an URL ?

For example a simple query

SELECT FirstNm, LastNm, email 
FROM dbo.Users

Open in new window


So it returns:
{
  "first_name": "Aleks",
  "last_name": "Demo",
  "email_addresses": [
    {
      "email_address": "support@domain.com"
    }
  ]
}

Open in new window


Is this posible from SQL or do I need to do this afterwards in ASP. I am using ASP Classic.
Comment
Watch Question

Sr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
from ASP

get result from sql as

SELECT FirstNm, LastNm, email 
FROM dbo.Users

Open in new window


then use asp

dim json_result as string
json_result = "{""first_name"": """ & RS("FirstNm") & """,  ""last_name"": """ & RS("LastNm") & """,  ""email_addresses"": [{""email_address"": """ & RS("email") & """}]}"

Open in new window


or try

dim json_result as string
json_result = "{first_name: '" & RS("FirstNm") & "',  last_name: '" & RS("LastNm") & "', email_addresses: [{email_address: '" & RS("email") & "'}]}"

Open in new window

Author

Commented:
Both return errors

Second and third:  Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

and the first one:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ': '.
Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark after the character string '
    }
  ]
}' as json_result
FROM dbo.Users
'.

I am using MS SQL 2012

Author

Commented:
I checked the first one and used:

SELECT
'{
  "first_name": "' + FirstNm + '",
  "last_name": "' + LastNm + '",
  "email_addresses": [
    {
      "email_address": "' + email + '"
    }
  ]
}' as json_result
FROM dbo.Users

Open in new window


It returns :
{    "first_name": "Jose",    "last_name": "JONES",    "email_addresses": [      {        "email_address": ""      }    ]  }

Which is valud JSON.  Thank you!

Author

Commented:
Not there is a syntax error. it is displayed at the bottom.