2012 SQL to JSON Select

Aleks
Aleks used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Sr. System Analyst
Commented:
from SQL

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

Open in new window

HainKurtSr. System Analyst

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

Aleks

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
Aleks

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!
Aleks

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial