July 15, 2016 at 9:00 am
Afternoon,
Thanks for your help in advance. When I run the following nothing is returned, not just for the field DOI, but even the string from dbo.GetHttp2(@urlComplete) . If I put the URL into chrome it does return json. Any idea why this could be.
CREATE function [dbo].[GetHttp2]
(
@url varchar(8000)
)
returns varchar(max)
WITH EXECUTE AS OWNER
as
BEGIN
DECLARE @win int
DECLARE @hr int
DECLARE @text varchar(max)
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
@urlComplete varchar(2000)
,@returnedCitation nvarchar(max)
SET @urlComplete = 'http://api.crossref.org/works?filter=from-pub-date:2016-01,issn:1943-2682'
select @returnedCitation = dbo.GetHttp2(@urlComplete) ;
SELECT *
FROM OPENJSON (@returnedCitation, N'$.message.items')
WITH (
DOI NVARCHAR(255) N'$.DOI')
Many Thanks,
Oliver
July 15, 2016 at 9:30 am
When I take the json from the browser and put it into a variable the json_query works fine. It appears to be something to do with the get http request.
declare @json varchar(max)
SET @json = '{
"status": "ok",
"message-type": "work-list",
"message-version": "1.0.0",
"message": {
"query": {},
"items-per-page": 20,
"items": [
{
"indexed": {},
"reference-count": 28,
"publisher": "Geological Society of America",
"issue": "2",
"published-print": {},
"DOI": "10.1130/g37349.1",
"type": "journal-article",
"created": {},
"page": "103-106",
"source": "CrossRef",
"title": [],
"prefix": "http://id.crossref.org/prefix/10.1130",
"volume": "44",
"author": [],
"member": "http://id.crossref.org/member/257",
"published-online": {},
"container-title": [],
"deposited": {},
"score": 1,
"subtitle": [],
"issued": {},
"alternative-id": [],
"URL": "http://dx.doi.org/10.1130/g37349.1",
"ISSN": [],
"subject": []
},
{
"indexed": {
"date-parts": [
[
2016,
5,
10
]
],
"date-time": "2016-05-10T15:40:16Z",
"timestamp": 1462894816620
},
"reference-count": 14,
"publisher": "Geological Society of America",
"issue": "3",
"published-print": {
"date-parts": [
[
2016,
3
]
]
},
"DOI": "10.1130/g37484.1",
"type": "journal-article",
"created": {
"date-parts": [
[
2016,
1,
23
]
],
"date-time": "2016-01-23T01:51:23Z",
"timestamp": 1453513883000
},
"page": "179-182",
"source": "CrossRef",
"title": [
"Storage thresholds for relative sea-level signals in the stratigraphic record"
],
"prefix": "http://id.crossref.org/prefix/10.1130",
"volume": "44",
"author": [
{
"affiliation": [],
"family": "Li",
"given": "Qi"
},
{
"affiliation": [],
"family": "Yu",
"given": "Lizhu"
},
{
"affiliation": [],
"family": "Straub",
"given": "Kyle M."
}
],
"member": "http://id.crossref.org/member/257",
"published-online": {
"date-parts": [
[
2016,
1,
22
]
]
},
"container-title": [
"Geology"
],
"deposited": {
"date-parts": [
[
2016,
5,
10
]
],
"date-time": "2016-05-10T14:45:40Z",
"timestamp": 1462891540000
},
"score": 1,
"subtitle": [],
"issued": {
"date-parts": [
[
2016,
1,
22
]
]
},
"alternative-id": [
"10.1130/G37484.1"
],
"URL": "http://dx.doi.org/10.1130/g37484.1",
"ISSN": [
"0091-7613",
"1943-2682"
],
"subject": [
"Geology"
]
},
{
"indexed": {
"date-parts": [
[
2016,
5,
10
]
],
"date-time": "2016-05-10T15:40:17Z",
"timestamp": 1462894817332
},
"reference-count": 24,
"publisher": "Geological Society of America",
"issue": "2",
"published-print": {
"date-parts": [
[
2016,
2
]
]
},
"DOI": "10.1130/g37423.1",
"type": "journal-article",
"created": {
"date-parts": [
[
2016,
1,
8
]
],
"date-time": "2016-01-08T03:49:13Z",
"timestamp": 1452224953000
},
"page": "163-166",
"source": "CrossRef",
"title": [
"Stronger or longer: Discriminating between Hawaiian and Strombolian eruption styles"
],
"prefix": "http://id.crossref.org/prefix/10.1130",
"volume": "44",
"author": [
{
"affiliation": [],
"family": "Houghton",
"given": "B.F."
},
{
"affiliation": [],
"family": "Taddeucci",
"given": "J."
},
{
"affiliation": [],
"family": "Andronico",
"given": "D."
},
{
"affiliation": [],
"family": "Gonnermann",
"given": "H.M."
},
{
"affiliation": [],
"family": "Pistolesi",
"given": "M."
},
{
"affiliation": [],
"family": "Patrick",
"given": "M.R."
},
{
"affiliation": [],
"family": "Orr",
"given": "T.R."
},
{
"affiliation": [],
"family": "Swanson",
"given": "D.A."
},
{
"affiliation": [],
"family": "Edmonds",
"given": "M."
},
{
"affiliation": [],
"family": "Gaudin",
"given": "D."
},
{
"affiliation": [],
"family": "Carey",
"given": "R.J."
},
{
"affiliation": [],
"family": "Scarlato",
"given": "P."
}
],
"member": "http://id.crossref.org/member/257",
"published-online": {
"date-parts": [
[
2016,
1,
7
]
]
},
"container-title": [
"Geology"
],
"deposited": {
"date-parts": [
[
2016,
5,
10
]
],
"date-time": "2016-05-10T14:49:53Z",
"timestamp": 1462891793000
},
"score": 1,
"subtitle": [],
"issued": {
"date-parts": [
[
2016,
1,
7
]
]
},
"alternative-id": [
"10.1130/G37423.1"
],
"URL": "http://dx.doi.org/10.1130/g37423.1",
"ISSN": [
"0091-7613",
"1943-2682"
],
"subject": [
"Geology"
]
}
],
"total-results": 159,
"facets": {}
}
}';
SELECT *
FROM OPENJSON(JSON_QUERY(@json, '$.message.items'))
WITH (
DOI NVARCHAR(255) N'$.DOI')
outputs:
DOI
10.1130/g37349.1
10.1130/g37484.1
10.1130/g37423.1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply