SQL Server 2016中的存储过程中的OPENJSON语法

问题描述

我正在遵循此页面上的最后一个示例 https://www.sqlservercentral.com/forums/topic/using-msxml2-serverxmlhttp-within-stored-procedure-to-grab -html-page-页面并保存到表中.

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.

它提取数据并将其加载到表中.我觉得我的语法在最后一步是错误的.代码提取了数据，但是我的OPENJSON是错误的，因此没有数据放入表中.任何帮助表示赞赏.

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&retmode=json&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

返回的数据是:

 {"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

添加了编辑数据

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'
  )

需要结果

  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

推荐答案

一种方法是使用openjson作为json字符串，然后将其与另一个openjson内部数组交叉应用:

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

结果:

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

这篇关于SQL Server 2016中的存储过程中的OPENJSON语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！