Link to home
Start Free TrialLog in
Avatar of Steve A
Steve AFlag for United States of America

asked on

Retrieving JSON data as OUT parameter in Sql Server CLR procedure

I have a CLR procedure that calls a web service within, and the web service returns a JSON string back with 3 'elements' within it.
I'd like to parse these 'elements' out into 3 separate columns.

When I run the procedure below, it returns with no data back but it should come back as:
{"First":"Jimmy","Last":"Black","Link":"http://test.aaz.com/login/empid=123456"}

--procedure executed below
declare @response nvarchar(max)

execute dbo.samplewsget 'http://dev.testme.com/externalinterface/get/extlink.ashx?auth=mysite&id=170' ,@response out

select @response 'response';  --this returns back as nothing when ran...


Ideally, @response should be - {"First":"Jimmy","Last":"Black","Link":"http://test.aaz.com/login/empid=123456"}

and later in some code, I want to parse these 3 out to different columns.
First - Jimmy
Last - Black
Link http://test.aaz.com/login/empid=123456

Can sql server 2008 handle this?  I would like to use 2014 or even 2016 but we are limited to only 2008 right now and a future
upgrade is in the works by our dba, so limited in just using 2008.

Tried different samples that looked good in creating the CLR, assemblies and even using json logic but got stuck due to limitations using 2008.
Any suggestions would be helpful.
Thanks,
MG
ASKER CERTIFIED SOLUTION
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial