Thank you, this works in your example but when I pull it from google it doesnt. Please try with the full script below. (I will replace the api key later).

CREATE function [dbo].[GetHttp]

(

@url varchar(8000)

)

returns varchar(8000)

WITH EXECUTE AS OWNER

as

BEGIN

DECLARE @win int

DECLARE @hr int

DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'

IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'

IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win

IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text

END

GO

DECLARE@ISBN varchar(150) = '9781847947154'

DECLARE

@urlPrefix varchar(300) = 'https://www.googleapis.com/books/v1/volumes?q=isbn:'

,@urlComplete varchar(2000)

,@returnedCitation nvarchar(4000)

SET @urlComplete = @urlPrefix + @ISBN + @urlKEY;

select @returnedCitation = dbo.GetHttp(@urlComplete) ;

-----AUTHORS

SELECT

OJV.

,OJV.value

,OJV.type

FROM OPENJSON(@returnedCitation,'$.items[0].volumeInfo.authors') OJV;

This only returns one result

keyvaluetype

0Philip Tetlock1