2012 SQL to JSON Select
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
So it returns:
Is this posible from SQL or do I need to do this afterwards in ASP. I am using ASP Classic.
For example a simple query
SELECT FirstNm, LastNm, email
FROM dbo.Users
So it returns:
{
"first_name": "Aleks",
"last_name": "Demo",
"email_addresses": [
{
"email_address": "support@domain.com"
}
]
}
Is this posible from SQL or do I need to do this afterwards in ASP. I am using ASP Classic.
Sr. System Analyst
CERTIFIED EXPERT
from ASP
get result from sql as
then use asp
or try
get result from sql as
SELECT FirstNm, LastNm, email
FROM dbo.Users
then use asp
dim json_result as string
json_result = "{""first_name"": """ & RS("FirstNm") & """, ""last_name"": """ & RS("LastNm") & """, ""email_addresses"": [{""email_address"": """ & RS("email") & """}]}"
or try
dim json_result as string
json_result = "{first_name: '" & RS("FirstNm") & "', last_name: '" & RS("LastNm") & "', email_addresses: [{email_address: '" & RS("email") & "'}]}"
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
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
I checked the first one and used:
It returns :
{ "first_name": "Jose", "last_name": "JONES", "email_addresses": [ { "email_address": "" } ] }
Which is valud JSON. Thank you!
SELECT
'{
"first_name": "' + FirstNm + '",
"last_name": "' + LastNm + '",
"email_addresses": [
{
"email_address": "' + email + '"
}
]
}' as json_result
FROM dbo.Users
It returns :
{ "first_name": "Jose", "last_name": "JONES", "email_addresses": [ { "email_address": "" } ] }
Which is valud JSON. Thank you!