OPENJSON syntax in a stored procedure in SQL Server 2016

Question

I'm following the last example on this page https://www.sqlservercentral.com/forums/topic/using-msxml2-serverxmlhttp-within-stored-procedure-to-grab-source-of-html-page-and-save-to-table.

It pulls the data and loads it into a table. I feel that my syntax is wrong in the last step. The code pulls the data but my OPENJSON is wrong so no data is put into a table. Any help is appreciated.

DECLARE @Object AS INT;
DECLARE @hr INT
DECLARE @json AS TABLE (Json_Table NVARCHAR(MAX))

DECLARE @pmidList NVARCHAR(MAX)
SET @PMIDLIST = '17784783,19505939,30166592' 

DECLARE @url NVARCHAR(MAX)
SET @url = 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=pubmed&amp;retmode=json&amp;id='+ @pmidList 

EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

EXEC @hr = sp_OAMethod @Object, 'open', NULL, 'get',
       @url OUT,
       'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

EXEC @hr = sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

EXEC @hr = sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object


INSERT INTO @json (Json_Table) 
    EXEC sp_OAGetProperty @Object, 'responseText'

-- select the JSON string
SELECT * FROM @json

-- Parse the JSON string
SELECT * 
FROM OPENJSON((SELECT * FROM @json), N'$.result')
WITH (  
  [uid] NVARCHAR(MAX) N'$.uids.uid',
  [title] NVARCHAR(MAX) N'$.uids.title' ,
  [sortpubdate] NVARCHAR(MAX) N'$.uids.sortpubdate',
  [epubdate] NVARCHAR(MAX) N'$.uids.epubdate'
 )

EXEC sp_OADestroy @Object

The data that is returned is:

 {"header": {"type": "esummary",version": "0.3"},"result": {"uids": ["17784783","19505939","30166592"],"17784783": {"uid": "17784783","pubdate": "2007 Aug","epubdate": "2007 Jul 20", "source": "PLoS Comput Biol","title": "Pathway... ",

And I'm wondering if it is even possible to parse

Added edited data

DECLARE @json NVARCHAR(MAX)
SET @json = '{
    "header": {
        "type": "esummary",
        "version": "0.3"
    },
    "result": {
        "uids": [
            "17784783",
            "19505939",
            "30166592"
        ],
        "17784783": {
            "uid": "17784783",
            "pubdate": "2007 Aug",
            "epubdate": "2007 Jul 20",
            "source": "PLoS Comput Biol",          
            "sortpubdate": "2007/08/01 00:00"          
        },
        "19505939": {
            "uid": "19505939",
            "pubdate": "2009 Aug 1",
            "epubdate": "2009 Jun 8",
            "source": "Bioinformatics",          
            "sortpubdate": "2009/08/01 00:00"
              },
        "30166592": {
            "uid": "30166592",
            "pubdate": "2019 Jan",
            "epubdate": "2018 Aug 30",
            "source": "Oncogene",
             "sortpubdate": "2019/01/01 00:00"

        }
    }
}'
print @json

SELECT * FROM OPENJSON((select * from @json), N'$.result')
WITH (  

  [uid] nvarchar(max) N'$.uids.uid' ,
  [sortpubdate] nvarchar(max) N'$.uids.sortpubdate',
  [epubdate] nvarchar(max) N'$.uids.epubdate'
  )

Results needed

  17784783         2007/08/01 00:00        2007 Jul 20
  19505939         2009/08/01 00:00        2009 Jun 8  
  30166592         2019/01/01 00:00        2018 Aug 30

If you are asking about your usage of `openjson`, why do we need to see all the code before that? If you could [edit] your question to include sample data as DDL+DML and expected results, it would be much easier to answer. — Zohar Peled, Aug 26 '19 at 11:42
I put all the code there so a person can copy, paste and execute into SQL Studio and get the exact output. Was that wrong? — Bill, Aug 26 '19 at 11:48
In general, no, that's not wrong. However, all these stored procedures require permissions that some of us might not have, and I don't know about most other users, but when I want to answer an SQL question, I usually try my solution on rextester or dbfiddle first - and you can't run these procedures on these services for security reasons. As a rule of thumb, if your question can be self contained then it should be. Don't send us to the internet to get the data, simply incorporate it in the question itself. — Zohar Peled, Aug 26 '19 at 11:52

score 1 · Accepted Answer · answered Aug 26 '19 at 12:25

One way to do it is with openjson for the json string, and cross apply it with another openjson the inner array:

SELECT [uid], [sortpubdate], [epubdate]
FROM OPENJSON(@json, N'$.result') As items
CROSS APPLY
-- parse each object in the array
OPENJSON(items.[value])
WITH(
    [uid] nvarchar(max) N'$.uid' ,
    [sortpubdate] nvarchar(max) N'$.sortpubdate',
    [epubdate] nvarchar(max) N'$.epubdate'
) As content
WHERE [key] <> 'uids' -- Get only the relevant content

Results:

uid         sortpubdate         epubdate
17784783    2007/08/01 00:00    2007 Jul 20
19505939    2009/08/01 00:00    2009 Jun 8
30166592    2019/01/01 00:00    2018 Aug 30

Add on at https://stackoverflow.com/questions/57659416/a-concatenation-openjson-in-a-sql-2016-stored-proc — Bill, Aug 26 '19 at 14:01

OPENJSON syntax in a stored procedure in SQL Server 2016

1 Answers1

Linked